Python to SQL and back again... an Object-Relational Mapping tale.

Howdy there everyone. It's me again, and today I am going to talk about how to work with databases and Python? Luckily Object-Relational Mapping (ORM) can help you do just that. ORM is a technique that allows you to interact with a database using Python code, which is much more convenient than writing SQL commands manually. In this tutorial, we'll take a look at how you can use ORM to interact with a SQLite database.

First, let's start with Python to SQL. We'll be using SQLite for this tutorial, but the same concepts apply to other databases as well. Here is a sample code to get started:

import sqlite3

db_connection = sqlite3.connect('my_database.db')
db_cursor = db_connection.cursor()

class ClassName:
    def __init__(self, var1, var2, id=None):
        self.id = id
        self.var1 = var1
        self.var2 = var2

    @classmethod
    def create_table(cls):
        sql = """
            CREATE TABLE IF NOT EXISTS table_name (
                id INTEGER PRIMARY KEY,
                var1 TEXT,
                var2 TEXT
            )
        """
        db_cursor.execute(sql)

    def save(self):
        sql = """
            INSERT INTO table_name (var1, var2)
            VALUES (?, ?)
        """
        db_cursor.execute(sql, (self.var1, self.var2))
        db_connection.commit()
        self.id = db_cursor.lastrowid

    @classmethod
    def create(cls, var1, var2):
        classname_instance = ClassName(var1, var2)
        classname_instance.save()
        return classname_instance

As you can see, we first import the SQLite module, then establish a connection to the database using sqlite3.connect(). Next, we define a class called ClassName with some instance variables, a class method for creating a table, a save method for inserting records, and a create method for creating new instances of the class and saving them to the database. The var1 and var2 parameters in the methods refer to the two columns in our table_name table.

To create the table in the database, you can call the create_table() method:

ClassName.create_table()

To create a new instance of ClassName and save it to the database, you can call the create() method:

classname_instance = ClassName.create('value1', 'value2')

Now let's move on to SQL to Python. Here's the sample code to get started:

class ClassName:
    all = []

    def __init__(self, var1, var2, id=None):
        self.id = id
        self.var1 = var1
        self.var2 = var2

    @classmethod
    def new_from_db(cls, row):
        classname_instance = cls(
            var1=row[1],
            var2=row[2],
            id=row[0]
        )
        return classname_instance

    @classmethod
    def get_all(cls):
        sql = """
            SELECT *
            FROM table_name
        """
        results = db_cursor.execute(sql).fetchall()
        cls.all = [cls.new_from_db(row) for row in results]
        return cls.all

    @classmethod
    def find_by_var1(cls, var1):
        sql = """
            SELECT *
            FROM table_name
            WHERE var1 = ?
            LIMIT 1
        """
        classname_instance = db_cursor.execute(sql, (var1,)).fetchone()
        if not classname_instance:
            return None
        return cls.new_from_db(classname_instance)

    @classmethod
    def find_or_create_by(cls, var1=None, var2=None):
        sql = """
            SELECT * FROM table_name
            WHERE (var1, var2) = (?, ?)
            LIMIT 1
        """
        classname_instance = db_cursor.execute(sql, (var1, var2)).fetchone()
        if not classname_instance:
            sql = """
                INSERT INTO table_name (var1, var2)
                VALUES (?, ?)
            """
            db_cursor.execute(sql, (var1, var2))
            return ClassName(
                var1=var1,
                var2=var2,
                id=db_cursor.lastrowid
            )
        return cls.new_from_db(classname_instance)

    def update(self):
        sql = """
            UPDATE table_name
            SET var1 = ?,
                var2 = ?
            WHERE id = ?
        """
        db_cursor.execute(sql, (self.var1, self.var2, self.id))

    @classmethod
    def drop_table(cls):
        sql = """
            DROP TABLE IF EXISTS table_name
        """
        db_cursor.execute(sql)

Here, we define the same ClassName class as before, but with some additional methods for fetching data from the database. We have a class variable called all to store all instances of ClassName, a new_from_db() method to create new instances of ClassName from database rows, and several class methods for fetching and creating instances.

The get_all() method selects all rows from the table_name table and returns them as a list of ClassName instances. The find_by_var1() method selects a row from the table_name table where var1 matches the given value and returns the corresponding ClassName instance.

The find_or_create_by() method tries to find an existing row with matching var1 and var2 values. If it finds one, it returns the corresponding ClassName instance. If it doesn't find one, it creates a new row with the given var1 and var2 values and returns a new ClassName instance.

Finally, we have an update() method that updates the var1 and var2 values of an existing row with the instance's id. And a drop_table() method to drop the table_name table.

I know there are several Automated ORM Managers (like SQLAlchemy or mySQL), so it can be challenging to see the value in knowing how to do it manually. However, your next job might not (most likely won't) be using the Automated ORM Manager that you already know. So knowing how to do it manually will come in handy until you can learn their specific software. Plus, it is faster to learn those when you understand what is happening behind the scenes.

Overall, ORM is a powerful tool that allows you to interact with databases in a more convenient way. Although it might seem intimidating at first, it's definitely worth the effort to learn. So keep pushing through, and remember that every small success is worth celebrating!