(+91) 80696 56578  |  CALLBACK REQUEST  |  (+91) 98905 02692  |  (+91) 97633 96156

All Courses

Download our e-book of Introduction To Python

Related Blog

Matplotlib - Object-oriented InterfaceMatplotlib - MultiplotsCNTK - Creating First Neural NetworkMatplotlib - Working with ImagesMicrosoft Cognitive Toolkit (CNTK) - CPU and GPUPython Forensics - Memory and ForensicsPython Blockchain - Scope and ConclusionDiscuss Microsoft Cognitive ToolkitMatplotlib - Twin AxesMatplotlib - Subplot2grid() Function View More

Top Discussion

Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? How to choosing the right estimator for the machine learning problem? How to Write Python ZIP File? How to extracting text from PDF file using python How can a web interface execute a .py file from a PHP file? What methods can we use to differentiate between correlated and uncorrelated variables in a regression analysis? How to leave/exit/deactivate a Python virtualenvironment Join Discussion

Top Courses

Master's In Artificial Intelligence Program in Collaboration With IBM

4.5 (1,292 Ratings)

589 Learners

Webinars

Why You Should Learn Data Science in 2023

Sep 30th (7:00 PM) 1117 Registered
More webinars

Python Data Persistence-SQLAlchemy

Neha Kumawat

2 years ago

Python Data Persistence-SQLAlchemy | insideAIML
Table of Contents
  • Introduction
  • Object Relation Mapper (ORM)
  • Databases
  • SQLAlchemy Data Type 

Introduction

          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 an 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.

Databases

          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
Databases | Insideaiml
The 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__
Output
'1.3.11'
Interactions with the database are done through Engine object obtained as a return value of create_engine() function.
engine =create_engine('sqlite:///mydb.sqlite')
SQLite allows the creation of an in-memory database. SQLAlchemy engine for the 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 a 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.

SQLAlchemy Data Type 

          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
The following code is the model class named Student that is mapped to the 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 Student table that has a corresponding structure, execute the create_all() method defined for the 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 the database, etc., are handled by a Session object.
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

sessionobj = Session()
Data stored in the Student object is physically added in the underlying table by the session’s add() method.
s1 = Student(name='Juhi', age=25, marks=200)

sessionobj.add(s1)

sessionobj.commit()
Here, is the entire code for adding records in the students table. As it is executed, the corresponding SQL statement log is displayed on the console.
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 object 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()
Like the Blog, then Share it with your friends and colleagues to make this AI community stronger. 
To learn more about nuances of Artificial Intelligence, Python Programming, Deep Learning, Data Science and Machine Learning, visit our insideAIML blog page.
Keep Learning. Keep Growing. 

Submit Review

Call us 98905 02692
Request a callback