Introduction

Hi everyone! Today we're going to talk about Python database operations. Have you ever encountered a situation where you wrote a bunch of code to process data, only to find out the data doesn't exist? Or accidentally deleted some important data? Don't worry, once you learn database operations, you can easily solve these problems. But wait, databases seem so complex and mysterious, can a beginner really get the hang of it? Don't rush, follow me step by step, and I'll definitely turn you from a beginner into an expert!

Starting Small

For beginners, the best way to learn is to start with something simple. For example, we can use Python's dictionary data structure to simulate a small database. Take a look at this example:

User = {
    'Name': {'Firstname': 'John', 'Lastname': 'Doe'},
    'Address': {'Street': '123 Main St', 'Zip': '12345', 'State': 'NY'},
    'CreditCard': {'CCtype': 'Visa', 'CCnumber': '1234-5678-9012-3456'},
}

Here we use a dictionary to store user information, including name, address, and credit card data. With this "small database", we can easily query, modify, and add data. For example, to change the user's address, we just need to update the User['Address'] dictionary.

But just one user's information isn't enough, right? If we want to store multiple users' data, we can use another dictionary to wrap all user data, like this:

Users = {
    1: {
        'Name': {'Firstname': 'John', 'Lastname': 'Doe'}, 
        'Address': {...},
        'CreditCards': [
            {'CCtype': 'Visa', 'CCnumber': '1234-...'},
            {'CCtype': 'Mastercard', 'CCnumber': '5678-...'}
        ]
    },
    2: {
        'Name': {...},
        ...
    }
}

Here we used a nested dictionary structure, allowing each user to have multiple credit cards. This way, we can simulate a one-to-many relationship quite well. How about that, doesn't it already look a bit like a database?

SQLite Recap

Of course, no matter how good simulating a database with dictionaries is, it's ultimately just a stopgap measure. If you want a more powerful and reliable solution, then you need to bring in Python's built-in SQLite database.

SQLite can be said to be the simplest database system, but it also has basic functions like creating tables, inserting data, and querying. Let's look at a small example:

import sqlite3


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


cursor.execute('''CREATE TABLE users
                  (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')


cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")
conn.commit()


cursor.execute("SELECT * FROM users")
print(cursor.fetchall())


conn.close()

Look, with just these few lines of code, we've created a users table and completed insert and query operations. Isn't it simple? If you want to delete or update data, you just need to execute the corresponding SQL statements.

However, as a lightweight database, SQLite's functionality is limited after all. If your application has a large amount of data, or needs to support multi-user concurrent access, then you need to consider using more powerful database systems like MySQL and PostgreSQL.

ORM Overview

When it comes to large-scale database systems, we can't help but mention ORM (Object-Relational Mapping) technology. The role of ORM is to map the table structure in the database to objects in the programming language, so we can operate the database in an object-oriented way without directly writing SQL statements.

The most popular ORM framework in the Python world is SQLAlchemy. Let's look at a small example using SQLAlchemy:

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


Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)


engine = create_engine('sqlite:///users.db')
Base.metadata.create_all(engine)


Session = sessionmaker(bind=engine)
session = Session()


new_user = User(name='Alice', age=30)
session.add(new_user)
session.commit()

See? We only need to define a User class, and SQLAlchemy will automatically create a users table based on this class. After that, we can perform all database operations through this class object, without having to write SQL statements ourselves.

Isn't it cool? I personally think the biggest advantage of ORM technology is that it improves code portability. Since all database operations are encapsulated in the ORM layer, if we need to migrate to another database in the future, we only need to change the connection string, without touching the upper-level code at all.

Of course, due to the extra overhead of object mapping in ORM, it may not show advantages in some scenarios with high performance requirements. But for most applications, the development efficiency improvement brought by ORM is very worthwhile.

Connecting to MySQL

We've been talking about SQLite, but in reality, large applications generally use relational databases like MySQL. So how do we connect to a MySQL database in Python?

Here we need to use a third-party library mysql-connector-python. After installing it, connecting to the database becomes very simple:

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM your_table")
for row in cursor.fetchall():
    print(row)

conn.close()

See, except for the different imported library, the other code is almost the same as SQLite. This is the advantage of the standard Python DB API, the unified interface allows us to easily switch between different databases.

However, if your application needs to support multiple database systems, I suggest you use an ORM framework like SQLAlchemy. It has built-in support for multiple databases and can minimize the work of database migration.

Transaction Processing

Finally, let's talk about database transactions. A transaction means that either all operations are successfully executed, or all are cancelled, and cannot be only partially executed. For example, in a transfer operation, if deducting money from account A is successful, but adding money to account B fails, this will lead to data inconsistency.

So we need to use transactions to ensure data integrity and consistency. In Python, handling transactions generally requires combining the commit() and rollback() methods:

import sqlite3

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

try:
    cursor.execute("INSERT INTO users (name, age) VALUES ('Charlie', 35)")
    # Other database operations
    conn.commit()  # Commit transaction
except Exception as e:
    print("An error occurred:", e)
    conn.rollback()  # Rollback transaction
finally:
    conn.close()

In this example, we used a try...except statement to catch exceptions. If any error occurs while executing SQL statements, rollback() will be executed to roll back all previous operations. Only when all operations are successful will commit() be executed to commit the transaction.

In this way, we can ensure data consistency and integrity. Transaction processing is particularly important in scenarios with high data security requirements, such as banking systems and e-commerce websites.

Conclusion

Alright, that's all for today's introduction. See, although Python database operations have a lot of theoretical knowledge, the actual code is still very simple and easy to understand. As long as you master the basic concepts and usage methods, you can overcome obstacles and excel in actual projects.

So, start practicing right away! Start by simulating a database with dictionaries, then try using SQLite, MySQL, and finally learn about ORM frameworks. Believe me, as long as you practice diligently, database operations will no longer be a challenge for you. Keep it up, and see you next time!