Learn How To Use SQLAlchemy In 5 Minutes(Python)

Why SQLAlchemy

As a python programmer, I occasionally have to use databases (mostly SQLite and MySQL) for storing data. However, due to the complexity of raw databases’ commands, the programming efficiency was not high until I started to use SQLAlchemy as the communicator between python and the databases. This short tutorial will show how easy it is to manipulate databases in Python.

How Data Stored in Databases

Data exists in databases as two-dimensional tables and a table has multiple columns and rows. A typical table is like this:

User = [
? ? ('1', 'Michael'),
? ? ('2', 'Bob'),
? ? ('3', 'Adam')
]        

A list named “User” represents a table, and each element represents a row on this table. Thus if we want to query the User’s name (id = 1), the database will return “Michael”.

Map a Table to Object

However, it is difficult to see the structure of this table. We could define a class and use an instance of this class to represent each row.

class User(object)
? ? def __init__(self, id, name):
? ? ? ? self.id = id
? ? ? ? self.name = name:
[
? ? User('1', 'Michael'),
? ? User('2', 'Bob'),
? ? User('3', 'Adam')
]
        

ORM(Object-Relational Mapping) is the shining pearl of Python. It maps a table in Relational Database to an object with the property of id and name.

SQLAlchemy will do all the work about mapping. We just need to manipulate the data in objects and do not need to remember lots of commands for each database (SQLalchemy will parse different methods when dealing with other databases). It will significantly improve our efficiency of coding.

How To Use SQLAlchemy

To install SQLAlchemy:

$ pip install sqlalchemy        

Quick Start

# import module
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# create the base object:
Base = declarative_base()

# define User object:
class User(Base):
? ? # set the name of table:
? ? __tablename__ = 'user'

? ? # set the structure of table:
? ? id = Column(String(20), primary_key=True)
? ? name = Column(String(20))

# initiate the connection with database, we just need to change the name of
# databases we want to connect:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')

# create session which is the instance of session maker:
DBSession = sessionmaker(bind=engine):        

The code above initialized SQLAlchemy and linked a table to a class. If there are tables more than one, we need to define other classes. For example, School:

class School(Base):
? ? __tablename__ = 'school'
? ? id = ...
? ? name = ...        

create_engine() uses a string to initialize the connection between Python and database:

"type-of-database:driver's-name-of-database//username:password@localhost:port/name of database"        

Let’s see how to insert a row to the table. Because of the ORM, adding a user instance is equivalent to inserting a row.

# create session
session = DBSession()

# create Userobject:
new_user = User(id='5', name='Bob')

# add to session:
session.add(new_user)

# commit the change:
session.commit()

# close session:
session.close():        

The steps are: getting the session, adding an object to the session, commit and closing the session.

Then how to query the data? With ORM, there will be an object return, we use the filter method to target the data we need.

# create session
session = DBSession()

# create Query，use filter method, criteria is within(). one()returns only one
# row，all() will return all rows meet the criteria given:
user = session.query(User).filter(User.id=='5').one()


# print the type of user class and info of name property:print('type:', 
# type(user))
print('name:', user.name)


# close Session. No need to commit without making any change:
session.close():        

The output:

type: <class '__main__.User'
name: Bob>        

The relational database will have multiple tables linked to achieving one-many or many-many relationships for advanced implementation. ORM framework could also support them by linking various classes together.?

For example, a user could have multiple books:

class User(Base)
? ? __tablename__ = 'user'

? ? id = Column(String(20), primary_key=True)
? ? name = Column(String(20))

? ? # one-many:
? ? books = relationship('Book')

class Book(Base):
? ? __tablename__ = 'book'

? ? id = Column(String(20), primary_key=True)
? ? name = Column(String(20))

? ? # there will be a foreign key adding to the table of "Book" to link it to User 
? ? user_id = Column(String(20), ForeignKey('user.id')):        

When we query the “Book” property of object “User”, the return will be a list with multiple “book” objects.

Conclusion

SQLAlchemy is the communicator between Python and databases. It uses the ORM framework to map rows on a table in the database to an object, the columns of this row will be the object’s property. Understanding the structure and the theory of relational databases will help the advanced implementation of ORM.?

References

1. SQLALCHEMY 1.4 Documentation: https://docs.sqlalchemy.org/en/14/core/engines.html
2. SQLAlchemy ORM Tutorial for Python Developers: https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/#SQLAlchemy-Introduction