Beginners Guide to SQLALchemy In Python For Database Operations

SQLALchemy is an open-source pythonic way of interacting with databases that was released under the MIT license. It is a cross-platform software implemented in python and gives flexible usage for application developers. Now no need of learning Database languages to work with Database, by using a python programming language we can perform all database operations more efficiently. By using SQLALchemy instead of learning a database programming language we can directly write queries for creating, manipulations, retrieving data, updating records using simple python scripts.

Topics we cover in this article

• Installing SQLALchemy
• Creating a table using SQLALchemy
• Inserting entities using SQLALchemy
• Deleting entities using SQLALchemy
• Updating records using SQLALchemy
• Retrieving Records from the table

Installing and using SQLALchemy

Using the pip method, we can install the SQLALchemy and by importing it, we can use it further in the program.

pip install sqlalchemy
import sqlalchemy as db

Hands-on implementation of  SQLALchemy in python

Now, we will implement the SQLAlchemy for database operations in the below sections.

Creating a Table

In the below code snippet, we will create a table using SQLALchemy using python.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///college.db', echo = True)

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String),
)
meta.create_all(engine)
print("these are columns in our table %s" %(students.columns.keys()))

Output:-

 

Here, we created a table named students with three columns id, name, lastname, and we are displaying the list of columns in the tables.

Inserting data to the table

Using the below code snippet we will insert data into the table 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

ins = students.insert()
ins = students.insert().values(name = 'prudhvi', lastname = 'varma')
conn = engine.connect()
result = conn.execute(ins)

 

We can see in the sixth line of output that we have added the name: prudhvi and last name: varma into the table.

In the below code snippet, we are inserting more values into the table. Here you can add your own entities and store them in the database.

conn.execute(students.insert(), [
   {'name':'Bhaskar', 'lastname' : 'guptha'},
   {'name':'vibhav','lastname' : 'kumar'},
   {'name':'prudhvi','lastname' : 'varma'},
   {'name':'manoj','lastname' : 'varma'},
])

Deleting data from tables

In the below code snippet, let us delete one person from the table. So based on the name we will filter the person and delete it from the table. 

from sqlalchemy.sql.expression import update
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)
conn = engine.connect()
stmt = students.delete().where(students.c.name == 'manoj')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

 

 

In the above output, we removed the row, filtered by his name that was ‘manoj’.

Updating records in the tables

In the below code snippet, we will update the details of the student. 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
   'students', 
   meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)
conn = engine.connect()
stmt=students.update().where(students.c.name=='vibhav').values(name='Dr.vaibhav')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

 

 

In the output, we updated the name Vaibhav to Dr.vaibhav

Retrieving Records from the table

In the below code snippet we are going to display the row based on  the name of the student

s = students.select().where(students.c.name=="prudhvi")
result = conn.execute(s)
for row in result:
   print (row)

 

In the output, we filtered the student with name 'prudhvi'.

Using the below code snippet we are going to display the row filtered by id.

s = students.select().where(students.c.id < 3)
result = conn.execute(s)
for row in result:
   print (row)

In the output, we filtered the students who have id < 3

Conclusion

In the above implementation, we learned how to create tables, insert entities, update records, delete rows, retrieving records using SQLALchemy in the python. As we could see that it is very easy to use with no database API required to perform the database operations. The above code snippets are implemented in the Google Colab environment. These implementations can also be performed in any python IDE’s.