How to Write SQL in Native Python

Technology vector created by freepik - www.freepik.com

Do you write a lot of SQL? Do you end up calling a lot of this SQL from within Python? Does the idea of being able to link with SQL databases and define, manipulate, and query using Python sound appealing?

SQLModel is a Python library for interacting with SQL databases in pure, native Python. Its design motivations include intuitiveness, ease of use, compatibility, and robustness. SQLModel employs Python type annotation, enforced and managed by Pydantic, as well as SQLAlchemy, "Python SQL toolkit and Object Relational Mapper," for its SQL interaction.

The library was written by Sebastián Ramírez, the author of FastAPI, and the 2 libraries are designed to interoperate seamlessly.

The key features of SQLModel, taken directly from the project's GitHub repository, are:

• Intuitive to write: Great editor support. Completion everywhere. Less time debugging. Designed to be easy to use and learn. Less time reading docs.
• Easy to use: It has sensible defaults and does a lot of work underneath to simplify the code you write.
• Compatible: It is designed to be compatible with FastAPI, Pydantic, and SQLAlchemy.
• Extensible: You have all the power of SQLAlchemy and Pydantic underneath.
• Short: Minimize code duplication. A single type annotation does a lot of work. No need to duplicate models in SQLAlchemy and Pydantic.

Let's have a quick look at how SQLModel works.

Create a SQLModel Model (SQL Table)

 
Want to create a table using SQLModel?

Here's a simple example of a code excerpt using SQLModel, from the library's GitHub repo.

from typing import Optional

from sqlmodel import Field, SQLModel

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

In the code above, the Hero class is a SQLModel object. SQLModel objects are equivalent to SQL tables in the SQLModel library. Class attributes, such as id and name, are table columns.

Create Rows (Table Instances)

 
Want to create a row in the table, aka a data instance?

Here's an example from the library's GitHub repo demonstrating how to do so. Note that each row is an instance of the above defined model.

hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

Note that only conventional Python code has been used to achieve the creation of a table and enter data into it row by row, with no SQL code needed as a go-between for our Python objects and the backend SQL database.

Write to the SQL Database

 
Note that no table or data has yet been written to any existing database, which can be accomplished as follows. The following full code example builds on the above code.

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

engine = create_engine("sqlite:///database.db")

SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    session.add(hero_1)
    session.add(hero_2)
    session.add(hero_3)
    session.commit()

The above code will write a new table to the existing database, and add the 3 "hero" entries to it.

 
Of course, there is an awful lot more that SQLModel can do. For a full treatment of what is possible with the library, and how to accomplish it, have a look at the full documentation.

 
 
Matthew Mayo (@mattmayo13) is a Data Scientist and the Editor-in-Chief of KDnuggets, the seminal online Data Science and Machine Learning resource. His interests lie in natural language processing, algorithm design and optimization, unsupervised learning, neural networks, and automated approaches to machine learning. Matthew holds a Master's degree in computer science and a graduate diploma in data mining. He can be reached at editor1 at kdnuggets[dot]com.