If you know your SQL and want to use it with Python, SQLAlchemy is a good tool to help you. You can start with plain SQL commands and later move to a higher level of abstraction.
This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.
Pre-requisites
To follow along, please make sure that you have SQLAlchemy installed and that you have the Northwind database for SQLite next to your Python script. I use an existing database as a starting point so that I can focus on the CRUD operations. Later in this series we look at ways to create a new database from scratch.
Attention
In this post I use the textual SQL feature of SQLAlchemy. That is a low-level way in which you write the whole SQL statements yourself. I find this a helpful intermediate step when you need to port your application to Python. SQLAlchemy Core has two other modes that allows you to use metadata to get the same results with a lot less typing – I cover this in the next posts.
Parametrized Queries
You can write your data directly in the SQL query string or concatenate it together. However, if you do that with user input you open up your application to SQL injection attacks. A much better way is to always use parameterised queries. That gives you slightly more to type but blocks malicious input like 1’ OR 1 = 1 at the database level.
Glue code
I use this script to put all the following parts together and run them in the right sequence:
|
1 2 3 4 5 6 7 8 9 |
if __name__ == '__main__': print("--- create() ---") id = create() print("--- read() ---") read(id) print("--- update() ---") update(id) print("--- delete() ---") delete(id) |
Create (or insert)
To create a row in the Employee table we can use this code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
from sqlalchemy import create_engine, text def create(): insert = text(""" INSERT INTO "main"."Employee" ("LastName", "FirstName", "Title", "BirthDate") VALUES (:last, :first, :title, :dob); """) connection_string = "sqlite:///Northwind_small.sqlite" engine = create_engine(connection_string, echo=False) with engine.begin() as con: con.execute(insert, {"last":"Davolio","first":"Nancy","title":"Sales","dob":"1980-05-29"}) new_id = con.execute(text("SELECT last_insert_rowid() AS Id;")).first() print(new_id) return new_id["Id"] |
The insert statement uses the Text() function of SQLAlchemy. This allows us to write SQL and tells SQLAlchemy that it does not need to do much transformation on it. Instead of writing the values directly in the statement, I use placeholder variables marked with a leading : (like :last).
We create an engine with the connection string, open a connection and execute our statement. The dictionary we pass to the execute() method contains our placeholder variables (this time without the leading :) and the values we want to use.
The second call of the execute method uses the SQLite function last_insert_rowid() to get the primary key of the newly created record and returns it. I will use this value throughout the examples to modify the same record.
Read (or select)
We can use this code to read from the database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
def read(id): select = text(""" SELECT Id, LastName, FirstName, Title, BirthDate FROM Employee WHERE LastName = :last AND FirstName IS NOT :first AND Id = :id """) connection_string = "sqlite:///Northwind_small.sqlite" engine = create_engine(connection_string, echo=False) with engine.begin() as con: for row in con.execute(select, {"last":"Davolio", "first":"Robert", "id":id}): print(row) print(row['LastName']) |
The select statement uses placeholder variables, otherwise it is the same statement as you would write it in your database management tool. We get a result set back that we can iterate over and print the whole row or a specific column.
Update
We can update a record in the database with this code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
def update(id): update = text(""" UPDATE Employee SET LastName = :last, FirstName = :first WHERE Id = :id """) connection_string = "sqlite:///Northwind_small.sqlite" engine = create_engine(connection_string, echo=False) with engine.begin() as con: con.execute(update, {"last":"D'Avolio","first":"Patricia","id":id}) with engine.begin() as con2: for row in con2.execute("SELECT Id, LastName, FirstName FROM Employee WHERE id = :id", {"id":id}): print(row) |
The update statement once more uses the placeholder values and executes the update as we did with insert and select.
You do not need the code for the second connection. I put it in so that we make sure the value is changed in the database.
Delete
We can delete a row with this code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
def delete(id): delete = text(""" DELETE from Employee WHERE Id = :id """) connection_string = "sqlite:///Northwind_small.sqlite" engine = create_engine(connection_string, echo=False) with engine.begin() as con: con.execute(delete, {"id":id}) with engine.begin() as con2: for row in con2.execute("SELECT Id, LastName, FirstName FROM Employee WHERE id = :id", {"id":id}): print(row) else: print("No rows found") |
Make sure that you always use a WHERE clause when you delete rows. Otherwise your table will be empty.
The second connection is optional. I only put it there that we can check if there is no longer an employee with this Id.
Transactions
SQLAlchemy has a nice feature that lets us use transactions without much effort. In the examples above I used the engine.begin() method to open the block I talk to the database. That method opens a transactions and makes a commit at the end of the block when no exception happened. We can see what goes on in the background by setting echo=True in the update method:
|
1 |
engine = create_engine(connection_string, echo=True) |
— update() —
21:06:42,310 INFO sqlalchemy.engine.Engine BEGIN (implicit)
21:06:42,311 INFO sqlalchemy.engine.Engine
UPDATE Employee
SET LastName = ?,
FirstName = ?
WHERE Id = ?21:06:42,312 INFO sqlalchemy.engine.Engine [generated in 0.00115s] (“D’Avolio”, ‘Patricia’, 34)
21:06:42,314 INFO sqlalchemy.engine.Engine COMMIT
In many examples you see a call to engine.connect() to get a connection. You can access your database that way, but when something goes wrong, you have no transaction in place to clean up the inconsistent state of your database. Therefore, I suggest you use engine.begin() to connect to your database – at least for the parts that change your data.
Next
If you know your SQL and do not intend to switch your database, this approach may be all you need. Next week we look at a more common way in which we can leverage metadata in SQLAlchemy and type a lot less SQL.
1 thought on “Python Friday #75: CRUD With Raw SQL in SQLAlchemy Core”