Introduction
In this article, we want to learn how to create tables, insert records, update records, delete records, retrieving records using SQLAlchemy in the python.
SQLAlchemy provides a nice “Pythonic”, uniform way of interacting with databases. Rather than dealing with the differences between specific dialects of traditional SQL such as MySQL or PostgreSQL, you can leverage the Pythonic framework of SQLAlchemy to streamline your workflow and more efficiently query your data. For this reason, it is worth learning even if you may already be familiar with traditional SQL.
Creating Database Engine and Connection
An engine is just a common interface to a database, and the information it requires to connect to one is contained in a connection string, such as mysql://root:abc123@localhost:3306/sakila. Here, mysql is the database driver, while sakila is a database in mysql.
Code
from sqlalchemy import create_engine
engine = create_engine(“mysql://root:abc123@localhost:3306/sakila”,echo = True)
Autoloading Tables from a Database
SQLAlchemy can be used to automatically load tables from a database using something called reflection. Reflection is the process of reading the database and building the metadata based on that information. It’s the opposite of creating a Table by hand and is very useful for working with existing databases.
To perform reflection, you need to import the Table object from the SQLAlchemy package. Then, you use this Table object to read your table from the engine and autoload the columns. Using the Table object in this manner is a lot like passing arguments to a function. For example, to autoload the columns with the engine, you have to specify the keyword arguments autoload=True and autoload_with=engine to Table().
Hence an object of MetaData class from SQLAlchemy Metadata is a collection of Table objects and their associated schema constructs. It holds a collection of Table objects as well as an optional binding to an Engine or Connection.
The create_all() function uses the engine object to create all the defined table objects and stores the information in metadata.
SQLAlchemy matches Python data to the best possible generic column data types defined in it. Some of the generic data types are −
- BigInteger
- Boolean
- Date
- DateTime
- Float
- Integer
- Numeric
- SmallInteger
- String
- Text
- Time
To create a students table in college database, use the following snippet −
Code
from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()
students = Table(
‘students’, meta,
Column(‘id’, Integer, primary_key = True),
Column(‘name’, String(20)),
Column(‘lastname’, String(20)),
)
meta.create_all(engine)
# Use the .table_names() method on the engine to print the table names
print(engine.table_names())
Output
2021-04-28 09:13:24,232 INFO sqlalchemy.engine.base.Engine SHOW FULL TABLES FROM `sakila`
2021-04-28 09:13:24,233 INFO sqlalchemy.engine.base.Engine ()
[‘actor’, ‘address’, ‘category’, ‘city’, ‘country’, ‘customer’, ‘film’, ‘film_actor’, ‘film_category’, ‘film_text’, ‘inventory’, ‘language’, ‘payment’, ‘rental’, ‘staff’, ‘store’, ‘students’]
Inserting Single Record
conn = engine.connect()
ins = students.insert().values(name = ‘Ravi’, lastname = ‘Kapoor’)
result=conn.execute(ins)
Output
2021-04-28 09:13:30,279 INFO sqlalchemy.engine.base.Engine INSERT INTO students (name, lastname) VALUES (%s, %s)
2021-04-28 09:13:30,280 INFO sqlalchemy.engine.base.Engine (‘Ravi’, ‘Kapoor’)
2021-04-28 09:13:30,289 INFO sqlalchemy.engine.base.Engine COMMIT
Inserting Multiple Records
Code
conn.execute(students.insert(), [
{‘name’:’Rajiv’, ‘lastname’ : ‘Khanna’},
{‘name’:’Komal’,’lastname’ : ‘Bhandari’},
{‘name’:’Abdul’,’lastname’ : ‘Sattar’},
{‘name’:’Priya’,’lastname’ : ‘Rajhans’},
])
Output
2021-04-27 14:19:21,064 INFO sqlalchemy.engine.base.Engine INSERT INTO students (name, lastname) VALUES (%s, %s)
2021-04-27 14:19:21,065 INFO sqlalchemy.engine.base.Engine ((‘Rajiv’, ‘Khanna’), (‘Komal’, ‘Bhandari’), (‘Abdul’, ‘Sattar’), (‘Priya’, ‘Rajhans’))
2021-04-27 14:19:21,074 INFO sqlalchemy.engine.base.Engine COMMIT
<sqlalchemy.engine.result.ResultProxy at 0x19c848d2610>
Selecting Data from Table
s = students.select()
result = conn.execute(s)
row = result.fetchone()
for row in result:
print (row)
Output
(2, ‘Rajiv’, ‘Khanna’)
(3, ‘Komal’, ‘Bhandari’)
(4, ‘Abdul’, ‘Sattar’)
(5, ‘Priya’, ‘Rajhans’)
Selecting Data from Table based on a Where Clause
s = students.select().where(students.c.id>2)
result = conn.execute(s)
for row in result:
print (row)
Output
2021-04-27 14:36:03,582 INFO sqlalchemy.engine.base.Engine SELECT students.id, students.name, students.lastname
FROM students
WHERE students.id > %s
2021-04-27 14:36:03,582 INFO sqlalchemy.engine.base.Engine (2,)
(3, ‘Komal’, ‘Bhandari’)
(4, ‘Abdul’, ‘Sattar’)
(5, ‘Priya’, ‘Rajhans’)
Selecting Data from Table using SQL Query
Using what we just learned about SQL and applying the .execute() method on our connection, we can leverage a raw SQL query to query all the records in our census table. The object returned by the .execute() method is a ResultProxy. On this ResultProxy, we can then use the .fetchall() method to get our results – that is, the ResultSet.
Code
# Creating a connection
connection = engine.connect()
# Build select statement: stmt
stmt = “select * from students”
# Execute the statement and fetch the results: results
results = connection.execute(stmt).fetchall()
# Print results
print(results)
Displaying Table Metadata
# Import Table
from sqlalchemy import Table, MetaData
metadata=MetaData()
# Reflect students table from the engine: students
students = Table(‘students’, metadata, autoload=True, autoload_with=engine)
# Print table metadata
print(repr(students))
# Print the column names
print(students.columns.keys())
# Print full table metadata
print(repr(metadata.tables[‘students’]))
Displaying Table Data
# Get the first row of the results by using an index: first_row
first_row = results[0]
# Print the first row of the results
print(first_row)
# Print the first column of the first row by using an index
print(first_row[0])
# Print the ‘state’ column of the first row by using its name
print(first_row[‘name’])
Deleting data from tables
conn = engine.connect()
stmt = students.delete().where(students.c.name == ‘Ravi’)
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()
Updating Records in the Table
conn = engine.connect()
stmt=students.update().where(students.c.name==’Rajiv’).values(name=’Dr. Rajiv’)
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()
Output
2021-04-27 17:39:37,604 INFO sqlalchemy.engine.base.Engine UPDATE students SET name=%s WHERE students.name = %s
2021-04-27 17:39:37,605 INFO sqlalchemy.engine.base.Engine (‘Dr. Rajiv’, ‘Rajiv’)
2021-04-27 17:39:37,612 INFO sqlalchemy.engine.base.Engine COMMIT
2021-04-27 17:39:37,617 INFO sqlalchemy.engine.base.Engine SELECT students.id, students.name, students.lastname
FROM students
2021-04-27 17:39:37,618 INFO sqlalchemy.engine.base.Engine ()
[(2, ‘Dr. Rajiv’, ‘Khanna’),
(3, ‘Komal’, ‘Bhandari’),
(4, ‘Abdul’, ‘Sattar’),
(5, ‘Priya’, ‘Rajhans’)]
Putting Data into Dataframes
# Creating a connection
connection = engine.connect()
# Build select statement : stmt
stmt = “select * from students”
# Execute the statement and fetch the results: results
results = connection.execute(stmt).fetchall()
# Print results
print(results)
# import pandas for creating dataframe
import pandas as pd
# Create a DataFrame from the results: df
df = pd.DataFrame(results)
# Set column names
df.columns = results[0].keys()
# Print the Dataframe
print(df)
Output
2021-04-28 09:01:05,054 INFO sqlalchemy.engine.base.Engine select * from students
2021-04-28 09:01:05,056 INFO sqlalchemy.engine.base.Engine ()
[(2, ‘Dr. Rajiv’, ‘Khanna’), (3, ‘Komal’, ‘Bhandari’), (4, ‘Abdul’, ‘Sattar’), (5, ‘Priya’, ‘Rajhans’)]
id name lastname
0 2 Dr. Rajiv Khanna
1 3 Komal Bhandari
2 4 Abdul Sattar
3 5 Priya Rajhans
References
Beginners Guide to SQLALchemy In Python For Database Operations (analyticsindiamag.com)
SQLAlchemy – Quick Guide – Tutorialspoint