Mysql by way of Python

In today’s post, we’ll be speaking about Python Mysql and the glory that is Python ORMs.

I was handed a new tasking that had me working to develop a report based on information in both a mysql database and a DynamoDB database. My first order of business is to learn how to pull information from the mysql database, and while working on my previous report generation tool, I had learned about ORMs. While investigating I found SQLAlchemy

After having spent a day learning how exactly to use, I felt it only proper that I write up a blog post on how to use it. Thus, our first step is to create an engine to the database we want to use:

This assumes that you’ve installed SQLAlchemy and pymysql
pip install sqlalchemy && pip install pymysql

#!/usr/bin/python
from sqlalchemy import create_engine
from sqlalchemy import Table
from sqlalchemy import MetaData

engine = create_engine(
		'mysql+pymysql://USERNAME_HERE:PASSWORD_HERE@HOSTNAME:PORT/DATABASE_NAME'
)

With that finished, our next step is to connect to the database and set up our metadata for it.

connection = engine.connect()

metadata = MetaData(bind=engine, reflect=True)

The metadata object is reflecting what we’d see in our database such as the rows and tables, however were we setting up a Database from scratch using SqlAlchemy we’d define the table in that object instead. The next step is to set up the table that we want to work with.

table = Table('Table_Name', metadata, autoload=True, autoload_with=engine)

Note that we imported Table and the earlier Metadata from sqlalchemy before we used them. Now that we have the table set we can get the column information from the table which can be useful when testing.

columns = locations.columns.keys()

Additionally, we can also run some queries against it such as this one that performs the equivilant of SELECT * FROM tablename and we can modify the data however we wish

query = select([tablename])

results = connection.execute(query)

for row in results:
		print("Items:", row[table.c.columnname])

There is a lot more than can be done with SQLAlchemy but hopefully this will help you get started using it!