Python – SQL Alchemy or plain SQL ?

Using SQL in Python reminded me of the “fun” I used to have in VBA MS Access, when I had to concatenate strings in VBA. Probably I was not the only one having “fun” and enjoying it, thus some good and wise people have provided a clever way to write SQL in Python, using the library sqlalchemy.

So, in order to show the difference between the usage of SQL in python with sqlite3 from one side and sqlalchemy on the other, I have decided to resolve the following small task with the two methods. Here is the task:

Make a program that generates a DB with one table.
1. The rows in the table are: id, username, password, balance, message, mail
Insert some data in the table
Show the inserted data on the console.

That was it – pretty easy and quite useful to see the way to useORM (Object-relational mapping) such as SQL Alchemy.

With SQL Alchemy, I am using one class, which generates the table and the users are added to the session. This is how it looks like in Python:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float
from sqlalchemy import create_engine
from sqlalchemy.orm import Session


Base = declarative_base()


class bank_clients(Base):
    __tablename__ = "clients"
    id = Column(Integer, primary_key=True)
    username = Column(String)
    password = Column(Integer)
    balance = Column(Float)
    message = Column(String)
    mail = Column(String)

    def __str__(self):
        return "{} {} {} {} {} {}".format(self.id, self.username, self.password, self.balance, self.message, self.balance)

    def __repr__(self):
        return self.__str__()

engine = create_engine("sqlite:///bank.db")
Base.metadata.create_all(engine)


session = Session(bind=engine)

session.add_all([
    bank_clients(id=1, username="Pesho", password="pw1", balance=200.21,
                 message="Hello!1", mail="my@ma.il"),
    bank_clients(id=2, username="Gosho", password="pw2", balance=200.22,
                 message="Hello!2", mail="my@ma.il"),
    bank_clients(id=3, username="Atanas", password="pw3", balance=200.23,
                 message="Hello!3", mail="my@ma.il"),
    bank_clients(id=4, username="Ivan", password="pw4", balance=200.24,
                 message="Hello!4", mail="my@ma.il"),
    bank_clients(id=5, username="Drago", password="pw5", balance=200.25,
                 message="Hello!5", mail="my@ma.il")
])

all_clients = session.query(
    bank_clients.id, bank_clients.username, bank_clients.balance)

for row in all_clients:
    print(row)

With Sqlite, I am using a class, which holds the connection and the cursor.

With the good old sql, the things are trivial – we are using a class again and three functions, which enable us to give sql requests to the sqlite3 DB:

import sqlite3


class bank_clients():

    def __init__(self):
        self.conn = sqlite3.connect("bank.db")
        self.cursor = self.conn.cursor()

    def generate_tables(self):

        sqlCreate = """CREATE TABLE clients(
                        id INTEGER PRIMARY KEY,
                        username TEXT,
                        password TEXT,
                        balance REAL,
                        message TEXT,
                        mail TEXT)"""

        self.cursor.execute(sqlCreate)
        self.conn.commit()

    def insert_into_table(self, id, username, password, balance, message, mail):
        sqlInsert = "INSERT into clients (id, username, password, balance, message, mail) values (?, ?, ?, ?, ?, ?)"
        self.cursor.execute(
            sqlInsert, (id, username, password, balance, message, mail))
        self.conn.commit()

    def show(self):
        sqlSelect = "SELECT * FROM clients"
        self.cursor.execute(sqlSelect)
        result = self.cursor.fetchall()

        for row in result:
            print(row)

viBank = bank_clients()
viBank.generate_tables()

viBank.insert_into_table(1, "Ivan", "pw1", "300", "I am Ivan", "@v.an")
viBank.insert_into_table(
    2, "Stoyan", "pw2", "312300.12", "I am not Ivan", "i@v.an")
viBank.insert_into_table(
    3, "Kristian", "pw3", "300312.12", "I am not Ivan", "i@v.an")
viBank.insert_into_table(
    4, "Dincho", "pw4", "300123.22", "I am not Ivan", "i@v.an")
viBank.insert_into_table(5, "Mincho", "pw5", "300.12", "I am Mincho", "i@v.an")
viBank.show()

When we take a look in the number of lines, it is more or less the same (although both solutions are a little far away from optimal code), but the SQL Alchemy code is cleaner. So learn its syntax and enjoy yourself! 🙂

Python – SQL Alchemy or plain SQL ?

Related posts: