Python Data Persistence - SQLAlchemy

Any relational database holds data in tables. The table structure defines data type of attributes which are basically of primary data types only which are mapped to corresponding built-in data types of Python. However, Python's user-defined objects can't be persistently stored and retrieved to/from SQL tables.

This is a disparity between SQL types and object oriented programming languages such as Python. SQL doesn't have equivalent data type for others such as dict, tuple, list, or any user defined class.

If you have to store an object in a relational database, it's instance attributes should be deconstructed into SQL data types first, before executing INSERT query. On the other hand, data retrieved from a SQL table is in primary types. A Python object of desired type will have to be constructed by using for use in Python script. This is where Object Relational Mappers are useful.

Object Relation Mapper (ORM)

An Object Relation Mapper (ORM) is an interface between a class and a SQL table. A Python class is mapped to a certain table in database, so that conversion between object and SQL types is automatically performed.

The Students class written in Python code is mapped to Students table in the database. As a result, all CRUD operations are done by calling respective methods of the class. This eliminates need to execute hard coded SQL queries in Python script.

ORM library thus acts as an abstraction layer over the raw SQL queries and can be of help in rapid application development. SQLAlchemy is a popular object relational mapper for Python. Any manipulation of state of model object is synchronized with its related row in the database table.

SQLALchemy library includes ORM API and SQL Expression Language (SQLAlchemy Core). Expression language executes primitive constructs of the relational database directly.

ORM is a high level and abstracted pattern of usage constructed on top of the SQL Expression Language. It can be said that ORM is an applied usage of the Expression Language. We shall discuss SQLAlchemy ORM API and use SQLite database in this topic.

SQLAlchemy communicates with various types of databases through their respective DBAPI implementations using a dialect system. All dialects require that an appropriate DBAPI driver is installed. Dialects for following type of databases are included −

  • Firebird
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL
  • SQLite
  • Sybase
Orm

Installation of SQLAlchemy is easy and straightforward, using pip utility.

pip install sqlalchemy

To check if SQLalchemy is properly installed and its version, enter following on Python prompt −

>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.3.11'

Interactions with database are done through Engine object obtained as a return value of create_engine() function.

engine =create_engine('sqlite:///mydb.sqlite')

SQLite allows creation of in-memory database. SQLAlchemy engine for in-memory database is created as follows −

from sqlalchemy import create_engine
engine=create_engine('sqlite:///:memory:')

If you intend to use MySQL database instead, use its DB-API module – pymysql and respective dialect driver.

engine = create_engine('mysql+pymydsql://root@localhost/mydb')

The create_engine has an optional echo argument. If set to true, the SQL queries generated by engine will be echoed on the terminal.

SQLAlchemy contains declarative base class. It acts as a catalog of model classes and mapped tables.

from sqlalchemy.ext.declarative import declarative_base
base=declarative_base()

Next step is to define a model class. It must be derived from base – object of declarative_base class as above.

Set __tablename__ property to name of the table you want to be created in the database. Other attributes correspond to the fields. Each one is a Column object in SQLAlchemy and its data type is from one of the list below −

  • BigInteger
  • Boolean
  • Date
  • DateTime
  • Float
  • Integer
  • Numeric
  • SmallInteger
  • String
  • Text
  • Time

Following code is the model class named as Student that is mapped to Students table.

#myclasses.py
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Numeric
base=declarative_base()
class Student(base):
   __tablename__='Students'
   StudentID=Column(Integer, primary_key=True)
   name=Column(String)
   age=Column(Integer)
   marks=Column(Numeric)

To create a Students table that has a corresponding structure, execute create_all() method defined for base class.

base.metadata.create_all(engine)

We now have to declare an object of our Student class. All database transactions such as add, delete or retrieve data from database, etc., are handled by a Session object.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
sessionobj = Session()

Data stored in Student object is physically added in underlying table by session’s add() method.

s1 = Student(name='Juhi', age=25, marks=200)
sessionobj.add(s1)
sessionobj.commit()

Here, is the entire code for adding record in students table. As it is executed, corresponding SQL statement log is displayed on console.

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from myclasses import Student, base
engine = create_engine('sqlite:///college.db', echo=True)
base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
sessionobj = Session()
s1 = Student(name='Juhi', age=25, marks=200)
sessionobj.add(s1)
sessionobj.commit()

Console output

CREATE TABLE "Students" (
   "StudentID" INTEGER NOT NULL,
   name VARCHAR,
   age INTEGER,
   marks NUMERIC,
   PRIMARY KEY ("StudentID")
)
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine COMMIT
INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO sqlalchemy.engine.base.Engine INSERT INTO "Students" (name, age, marks) VALUES (?, ?, ?)
INFO sqlalchemy.engine.base.Engine ('Juhi', 25, 200.0)
INFO sqlalchemy.engine.base.Engine COMMIT

The session object also provides add_all() method to insert more than one objects in a single transaction.

sessionobj.add_all([s2,s3,s4,s5])
sessionobj.commit()

Now that, records are added in the table, we would like to fetch from it just as SELECT query does. The session object has query() method to perform the task. Query object is returned by query() method on our Student model.

qry=seesionobj.query(Student)

Use the get() method of this Query object fetches object corresponding to given primary key.

S1=qry.get(1)

While this statement is executed, its corresponding SQL statement echoed on the console will be as follows −

BEGIN (implicit)
SELECT "Students"."StudentID" AS "Students_StudentID", "Students".name AS 
   "Students_name", "Students".age AS "Students_age", 
   "Students".marks AS "Students_marks"
FROM "Students"
WHERE "Products"."Students" = ?
sqlalchemy.engine.base.Engine (1,)

The query.all() method returns a list of all objects which can be traversed using a loop.

from sqlalchemy import Column, Integer, String, Numeric
from sqlalchemy import create_engine
from myclasses import Student,base
engine = create_engine('sqlite:///college.db', echo=True)
base.metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
sessionobj = Session()
qry=sessionobj.query(Students)
rows=qry.all()
for row in rows:
   print (row)

Updating a record in the mapped table is very easy. All you have to do is fetch a record using get() method, assign a new value to desired attribute and then commit the changes using session object. Below we change marks of Juhi student to 100.

S1=qry.get(1)
S1.marks=100
sessionobj.commit()

Deleting a record is just as easy, by deleting desired object from the session.

S1=qry.get(1)
Sessionobj.delete(S1)
sessionobj.commit()
Advertisements