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

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)

Recommended Posts

Try using SQLAlchemy + MySQL (Part 1)
Try using SQLAlchemy + MySQL (Part 2)
Try using Pillow on iPython (Part 1)
Try using Pillow on iPython (Part 2)
Try using Pillow on iPython (Part 3)
Connect to MySQL using Flask SQLAlchemy
Try using Tkinter
Try using docker-py
Try using PDFMiner
Try using geopandas
Try using Selenium
Try using scipy
Try using pandas.DataFrame
Try using django-swiftbrowser
Try using matplotlib
Try using tf.metrics
Try using PyODE
Try using the Python web framework Tornado Part 1
Try using the Python web framework Tornado Part 2
Try using virtualenv (virtualenvwrapper)
[Azure] Try using Azure Functions
Try using virtualenv now
Try using W & B
Try using Django templates.html
[Kaggle] Try using LGBM
Try using Python's feedparser.
Try using Python's Tkinter
Try using Tweepy [Python2.7]
Try using Pytorch's collate_fn
Try using PythonTex with Texpad.
Try normal Linux programming Part 7
[Python] Try using Tkinter's canvas
Try using Jupyter's Docker image
Try using scikit-learn (1) --K-means clustering
Try function optimization using Hyperopt
Try using Azure Logic Apps
[Kaggle] Try using xg boost
Try using the Twitter API
Try using OpenCV on Windows
Try using Jupyter Notebook dynamically
Try normal Linux programming Part 3
Try tweeting automatically using Selenium.
Try using the Twitter API
Try normal Linux programming Part 4
Try using Django's template feature
Try using the PeeringDB 2.0 API
Try using Pelican's draft feature
Try normal Linux programming Part 6
Try using pytest-Overview and Samples-
Try using folium with anaconda
Generate SQLAlchemy table definition from existing MySQL server using sqlacodegen
Try using Janus gateway's Admin API
[Statistics] [R] Try using quantile regression.
Try using Spyder included in Anaconda
Try using design patterns (exporter edition)
Try deep learning with TensorFlow Part 2
Try using LevelDB in Python (plyvel)
Try using pynag to configure Nagios
Try using ArUco on Raspberry Pi
DB table insertion process using sqlalchemy
[Sakura rental server] Try using flask.