[PYTHON] Try using SQLAlchemy + MySQL (Part 1)

I want to use MySQL with Python.

When I searched for a Python O / R mapper, ** SQLAlchemy ** seems to be famous, so I will use it this time.

The official tutorial is in English, but there was a blog that explained it carefully in Japanese, so I will proceed while referring to this as well.

Official tutorial http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

Consideration about Symfoware blog> How to use SQLAlchemy 1 Mapping of single table and addition of data http://symfoware.blog68.fc2.com/blog-entry-1373.html

Operating environment

Mac OS X 10.11.5
Python 3.5.1
MySQL Ver 14.14 Distrib 5.7.11, for osx10.11 (x86_64) using EditLine wrapper
SQLAlchemy 1.1.0
PyMySQL 0.7.4

Advance preparation

Install SQLAlchemy

$ pip install sqlalchemy

Install PyMySQL (MySQL driver) (Be careful when selecting because some DB drivers are not yet compatible with Python 3.x series!)

$ pip install PyMySQL

Try to write the code

First, when I wrote the code based on the sample of "Introduction to Python 3 (O'Reilly)", I was addicted to various things, so I will write a note.

`sqlalchemy_test.py`


import sqlalchemy as sa

url = 'mysql+pymysql://root:@localhost/test_db?charset=utf8'
engine = sa.create_engine(url, echo=True)

engine.execute('DROP TABLE zoo')
engine.execute('CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT)')

#In the SQL statement "?Cannot be used, so instead%s ”is used
ins = "INSERT INTO zoo (critter, count, damages) VALUES (%s, %s, %s)"
engine.execute(ins, "duck", 10, 0.0)
engine.execute(ins, "Bear", 2, 1000.0)
engine.execute(ins, "Weasels", 1, 2000.0)

rows = engine.execute('SELECT * FROM zoo')

for row in rows:
    print(row)

I want to use Japanese

If you add "? Charset = utf8" to the argument url of the create_engine function, you can use Japanese as well.

[Error message]

`UnicodeEncodeError: 'latin-1' codec can't encode characters in position 51-53: ordinal not in range(256)`

"?" Cannot be used in SQL statement

You cannot use "?" In SQL statements, such as "VALUES (?,?,?)". Use "% s" instead. (It looks like a bug in SQLAlchemy.)

[Error message]

`not all arguments converted during string formatting`

The script file name should not be "sqlalchemy.py"

The script file name should not be "sqlalchemy.py". Also, there should not be a file named "sqlalchemy.py" in the directory where the script file is located.

[Error message]

`module 'sqlalchemy' has no attribute 'create_engine'`

Try using SQLAlchemy + MySQL (Part 2)