SQLAlchemy — Python Tutorial
We often encounter data as Relational Databases. To work with them we generally would need to write raw SQL queries, pass them to the database engine and parse the returned results as a normal array of records.
SQLAlchemy provides a nice “Pythonic” way of interacting with databases. So rather than dealing with the differences between specific dialects of traditional SQL such as MySQL or PostgreSQL or Oracle, you can leverage the Pythonic framework of SQLAlchemy to streamline your workflow and more efficiently query your data.
Installing The Package
pip install sqlalchemyConnecting to a database
To start interacting with the database we first we need to establish a connection.
import sqlalchemy as db
engine = db.create_engine('dialect+driver://user:pass@host:port/db')Some examples of connecting to various databases can be found here
Viewing Table Details
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.
example
Querying
Table and MetaData have already been imported. The metadata is available as metadata.
ResultProxy: The object returned by the .execute() method. It can be used in a variety of ways to get the data returned by the query.
ResultSet: The actual data asked for in the query when using a fetch method such as .fetchall() on a ResultProxy.
Dealing with Large ResultSet
We use .fetchmany() to load optimal no of rows and overcome memory issues in case of large datasets
while flag:
partial_results = ResultProxy.fetchmany(50)
if(partial_results == []):
flag = False
//
code
//
ResultProxy.close()Convert to dataframe
df = pd.DataFrame(ResultSet)
df.columns = ResultSet[0].keys()Filtering data
Lets see some examples of raw SQLite Queries and queries using SQLAlchemy.
where
SQL :
SELECT * FROM census
WHERE sex = FSQLAlchemy :
db.select([census]).where(census.columns.sex == 'F')
in
SQL :
SELECT state, sex
FROM census
WHERE state IN (Texas, New York)SQLAlchemy :
db.select([census.columns.state, census.columns.sex]).where(census.columns.state.in_(['Texas', 'New York']))
and, or, not
SQL :
SELECT * FROM census
WHERE state = 'California' AND NOT sex = 'M'SQLAlchemy :
db.select([census]).where(db.and_(census.columns.state == 'California', census.columns.sex != 'M'))
order by
SQL :
SELECT * FROM census
ORDER BY State DESC, pop2000SQLAlchemy :
db.select([census]).order_by(db.desc(census.columns.state), census.columns.pop2000)
functions
SQL :
SELECT SUM(pop2008)
FROM censusSQLAlchemy :
db.select([db.func.sum(census.columns.pop2008)])
other functions include avg, count, min, max…
Get Vinay Kudari’s stories in your inbox
Join Medium for free to get updates from this writer.
group by
SQL :
SELECT SUM(pop2008) as pop2008, sex
FROM censusSQLAlchemy :
db.select([db.func.sum(census.columns.pop2008).label('pop2008'), census.columns.sex]).group_by(census.columns.sex)
distinct
SQL :
SELECT DISTINCT state
FROM censusSQLAlchemy :
db.select([census.columns.state.distinct()])
case & cast
The case() expression accepts a list of conditions to match and the column to return if the condition matches, followed by an else_ if none of the conditions match.
cast() function to convert an expression to a particular type
example
We use .scalar to the result when the result contains only single value
joins
If you have two tables that already have an established relationship, you can automatically use that relationship by just adding the columns we want from each table to the select statement.
select([census.columns.pop2008, state_fact.columns.abbreviation])example
Creating and Inserting Data into Tables
By passing the database which is not present, to the engine then sqlalchemy automatically creates a new database.
Updating data in Databases
db.update(table_name).values(attribute = new_value).where(condition)Delete Table
db.delete(table_name).where(condition)Dropping a Table
table_name.drop(engine) #drops a single tablemetadata.drop_all(engine) #drops all the tables in the database
IPython Notebook’s and other assets of this story can be found here
See ya again 😃








