First Encounter with Databases

Hey, today we're going to talk about an important topic - database operations. I'm sure you've heard the saying "data is the new oil of the digital era," right? That's correct. With the advent of the big data era, the value of data is being recognized by more and more people. As programmers, we deal with data every day, so mastering database operation skills becomes particularly important.

You say you already know how to create simple database structures using dictionaries? That's great, it's a good start. But dictionaries alone are certainly not enough, because real database systems are far more complex than that. So let's start with the most basic SQL databases.

The Power of SQLAlchemy

SQLAlchemy is a very powerful ORM (Object-Relational Mapping) library in the Python programming language that can greatly simplify our database operations. Are you already impressed by its powerful features?

I know you must be curious about how SQLAlchemy is used, so let me show you a small example:

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()


users = session.query(User).all()
for user in users:
    print(user.name, user.age)

Look, with just a few simple lines of code, we've completed the entire process of connecting to the database, creating tables, inserting data, and querying data! Isn't that cool?

I know you must be curious about how SQLAlchemy works. Don't worry, I'll explain it to you right away:

1. First, we imported some necessary modules and functions.
2. Then, we defined a User model class that inherits from Base. This class is equivalent to a table in the database.
3. Next, we use the create_engine function to connect to a SQLite database file.
4. Calling Base.metadata.create_all(engine) automatically creates the database table structure based on the model class.
5. Creating a Session instance allows us to operate on the database through it.
6. Finally, we created a new User object, added it to the session, and then committed it to the database. Querying data is also done through the Session.

See, SQLAlchemy maps all database operations to object operations, isn't it intuitive? You don't need to write native SQL statements at all, everything can be done with Python code!

Database Operations in Django

Besides SQLAlchemy, Django, the well-known web framework, also provides powerful database support functionality. I know you must be eager to understand how Django manages databases, so let's look at an example:

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

First, we defined a User model class in the models.py file, which inherits from django.db.models.Model. Each class attribute corresponds to a field in the database table.

Next, we need to perform database migration to synchronize the model to the database:

python manage.py makemigrations
python manage.py migrate

The first command generates a migration file that describes the changes needed for the database. The second command actually executes these changes, creating or updating the database table structure.

Finally, we can perform various operations on the database in Django view functions or model managers:

user = User(name='Bob', age=25)
user.save()


users = User.objects.all()


user = User.objects.get(id=1)
user.age = 26
user.save()


user = User.objects.get(id=1)
user.delete()

You see, Django's model layer abstracts the database into Python objects, and we can perform CRUD operations on the database just like operating on ordinary objects. Isn't that convenient?

SQLite and Flask-SQLAlchemy

Of course, in addition to SQLAlchemy and Django ORM mentioned above, we can also directly use Python's built-in sqlite3 module to operate SQLite databases. SQLite database files are single disk files that don't require a separate server process, so they're very lightweight.

Let's look at a simple example:

import sqlite3


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


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


c.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
conn.commit()


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


conn.close()

As you can see, using the sqlite3 module requires us to manually execute SQL statements to operate the database. This is a bit more troublesome than using an ORM framework, but it's also more flexible.

Finally, let's look at an example using the Flask framework and integrating the Flask-SQLAlchemy extension:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

@app.route('/')
def index():
    # Add data
    new_user = User(username='JohnDoe') 
    db.session.add(new_user)
    db.session.commit()

    # Query data
    users = User.query.all()
    return str(users)

if __name__ == '__main__':
    db.create_all()
    app.run(debug=True)

As you can see, the usage of Flask-SQLAlchemy is very similar to SQLAlchemy itself. We only need simple configuration to conveniently integrate database operation functionality into Flask applications.

Summary

Alright, that's all for our introduction today. Through the examples above, I believe you now have a certain understanding of common database operation methods in Python. Whether using an ORM framework or directly operating SQL, database operations are no longer difficult once you master the correct methods.

Of course, real database systems have many advanced features, such as transaction processing, connection pooling, database sharding, and so on. But these are left for you to explore on your own! The road of learning is long, but as long as you persist, you can definitely become an expert in database operations. Keep going!