World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
+256704300651- Psychic lost love Spell Caster // Divorce Spell // Marriage Proposal +256704300651 == WASHINGTON DC// TOP DEATH SPELLS @POWERFUL REVENGE DEATH SPELL CASTER IN USA,CANADA, UK, CALIFONIA, MEXICO, IRELAND, NY, CANADA, LONDON, SWEDEN, BOTSWANA 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? ((+256704300651}} INSTANT DEATH SPELL CASTER / REVENGE SPELL/ VOODOO A SPELL CASTER/╬ ₩௹]+256704300651₩௹]╬MAMA ZAMIE A TRADITIONAL HEALER /SANGOMA AND A SPIRITUAL HEALER in BELGIUM, +256704300651.. === Revenge Death spells caster In Denmark / Sweden / Germany / spell caster, revenge spell, spell caster review, witchcraft, psychic, magic forum. ↩⁜↪ +91-9352799565↩⁜↪ Divorce Problem Solution IN mumbai Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Why You Should Learn Data Science in 2023?
Jun 8th (7:00 PM) 289 Registered
Python Data Persistence-SQLAlchemy
Neha Kumawat
a year ago
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
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.