Skip to main content

Working with Databases: SQL and ORM

Introduction​

Working with databases is a crucial aspect of most programming projects. In Python, we have several options for interacting with databases, including SQL (Structured Query Language) and ORM (Object Relational Mapping). This tutorial will guide you through the basics of these two methods for managing data in Python.

SQL in Python​

SQL is a standard language for managing data in relational databases. In Python, we use a library called SQLite3 that allows us to create, read, update, and delete (CRUD) records in an SQL database.

Connecting to a Database​

Before we can interact with a database, we need to connect to it. Here's how we'd do that:

import sqlite3
conn = sqlite3.connect('example.db')

In the example above, we're connecting to a database file named example.db. If the file doesn't exist, SQLite3 will create it for us.

Creating a Table​

Once connected, we can create a table using SQL commands. Here's an example:

c = conn.cursor()

c.execute('''
    CREATE TABLE stocks
    (date text, trans text, symbol text, qty real, price real)
''')

conn.commit()

This code creates a new table named stocks with five columns.

Inserting Data​

We can insert data into our table using SQL's INSERT INTO statement:

c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
conn.commit()

Retrieving Data​

To retrieve data from our database, we use SQL's SELECT statement:

c.execute('SELECT * FROM stocks WHERE symbol="RHAT"')
print(c.fetchone())

ORM in Python​

While SQL is powerful, it can be cumbersome to use in an object-oriented language like Python. This is where ORM comes in. ORM allows us to interact with our database like we would with Python objects.

There are several ORM libraries in Python, but we'll use SQLAlchemy for this tutorial.

Creating a Session​

First, we need to create a session to interact with the database:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)

session = Session()

Defining a Class​

Next, we define a Python class that corresponds to our stocks table:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Stock(Base):
    __tablename__ = 'stocks'

    id = Column(Integer, primary_key=True)
    date = Column(String)
    trans = Column(String)
    symbol = Column(String)
    qty = Column(Integer)
    price = Column(Integer)

Inserting Data​

To insert data, we create an instance of our class and add it to our session:

new_stock = Stock(date='2006-01-05', trans='BUY', symbol='RHAT', qty=100, price=35.14)

session.add(new_stock)
session.commit()

Retrieving Data​

We can retrieve data by querying our session:

stocks = session.query(Stock).filter(Stock.symbol == 'RHAT').all()

for stock in stocks:
    print(stock.date, stock.trans, stock.qty, stock.price)

Conclusion​

In this article, we've discussed two methods of interacting with databases in Python: SQL and ORM. Both methods have their pros and cons and are useful in different scenarios, so it's a good idea to be familiar with both. Happy coding!