Hello everyone, today we're going to talk about the basics of Python database operations. I believe many of you will encounter database read and write operations when learning Python. But don't worry, as long as you master some basic concepts and methods, you'll be able to handle databases with ease.

Database? It's Just Storing Data

First, we need to understand, what is a database? A database is essentially a tool for storing and managing data. For example, you can imagine it as a super mega warehouse, storing thousands of products (data). And the database management system (DBMS) is like the warehouse manager, responsible for operations such as storing, searching, and shipping these products.

Of course, a database is more than just a simple warehouse. It can also ensure data security, consistency, support high concurrent access, and many other powerful functions. But let's not talk about these for now, let's focus on the most basic CRUD operations.

Simulating a Simple Database with Python Code

Before we start operating on a database, let's simulate a simple database with Python code to understand the design concept of databases. We can use Python's built-in dict data structure to store user information:

database = {
    'user1': {
        'name': 'Zhang San',
        'age': 25,
        'email': '[email protected]',
        'credit_cards': [
            {'number': '1234567890', 'expiry': '05/28', 'cvv': 123},
            {'number': '9876543210', 'expiry': '11/25', 'cvv': 456}
        ]
    },
    'user2': {
        'name': 'Li Si',
        'age': 30,
        'email': '[email protected]',
        'credit_cards': [
            {'number': '5555555555', 'expiry': '06/27', 'cvv': 789}
        ]
    }
}

This database dictionary is now our "database". It stores information for two users, and each user is a nested dictionary containing fields such as name, age, email, etc. We used a list to store credit card information because a user might have multiple credit cards.

This nested dictionary structure can intuitively reflect the concepts of "tables" and "fields" in a database. Of course, real databases are more complex and robust, but from this simple example, you can roughly understand the design concept of databases.

Advanced: General Database Operation Process

Alright, after saying so much, we can finally get to the main point! Let's look at the basic process of operating real databases using Python.

First, you need to import the corresponding database driver library. For example, you can use the built-in sqlite3 module in Python to operate SQLite databases. For MySQL, you need to install the third-party library mysql-connector-python. For PostgreSQL, the corresponding library is psycopg2.

Next is to establish a database connection and create a cursor object. The cursor allows you to execute SQL statements in the database, retrieve query results, and so on. The basic process is like this:

import sqlite3


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


cursor = conn.cursor()


cursor.execute("CREATE TABLE users (name TEXT, age INTEGER)")
cursor.execute("INSERT INTO users VALUES ('Zhang San', 25)")


cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
print(results)  # [('Zhang San', 25)]


conn.commit()
conn.close()

In real scenarios, we generally use parameterized queries to avoid SQL injection attacks, and use transaction management to ensure data consistency and integrity. Also, it's recommended to use the with statement to automatically manage the lifecycle of database connections, which can avoid resource leaks caused by missed connections.

Summary

Alright, that's all for today. We learned what a database is, simulated a simple database structure with Python code, and understood the general database operation process.

Database operations may seem like a behemoth, but as long as you master the basics step by step, you can handle it. Next time we'll talk about specific operation methods for some common databases, as well as some database design and optimization techniques. Oh, if you have any questions, feel free to ask me!