Python数据库编程全指南SQLite和MySQL实践

本文分享自华为云社区《
Python数据库编程全指南SQLite和MySQL实践
》，作者：柠檬味拥抱。

1. 安装必要的库

首先，我们需要安装Python的数据库驱动程序，以便与SQLite和MySQL进行交互。对于SQLite，Python自带了支持；而对于MySQL，我们需要安装额外的库，如
mysql-connector-python
。

# 安装 MySQL 连接器

pip install mysql-connector-python

2. 连接SQLite数据库

SQLite是一种轻量级的嵌入式数据库，无需服务器即可使用。以下是如何连接并操作SQLite数据库的示例代码：

import sqlite3



# 连接到 SQLite 数据库

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



# 创建一个游标对象

cursor=conn.cursor()



# 创建表

cursor.execute('''CREATE TABLE IF NOT EXISTS users
                  (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
# 插入数据

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))

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



# 查询数据

cursor.execute("SELECT * FROM users")

rows=cursor.fetchall()for row inrows:

    print(row)



# 提交并关闭连接

conn.commit()

conn.close()

3. 连接MySQL数据库

MySQL是一种常见的关系型数据库管理系统。使用Python连接MySQL需要使用相应的库，比如
mysql-connector-python
。以下是连接并操作MySQL数据库的示例代码：

import mysql.connector



# 连接到 MySQL 数据库

conn=mysql.connector.connect(

    host="localhost",

    user="username",

    password="password",

    database="mydatabase")



# 创建一个游标对象

cursor=conn.cursor()



# 创建表

cursor.execute('''CREATE TABLE IF NOT EXISTS users
                  (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)''')
# 插入数据

sql= "INSERT INTO users (name, age) VALUES (%s, %s)"val= ("Alice", 30)

cursor.execute(sql, val)



# 查询数据

cursor.execute("SELECT * FROM users")

rows=cursor.fetchall()for row inrows:

    print(row)



# 提交并关闭连接

conn.commit()

conn.close()

4. 代码解析

连接数据库：使用
sqlite3.connect()
连接SQLite数据库，使用
mysql.connector.connect()
连接MySQL数据库。
创建表：通过执行SQL语句创建表，使用
cursor.execute()
方法执行。
插入数据：执行插入数据的SQL语句，使用
cursor.execute()
方法并传入参数。
查询数据：执行查询数据的SQL语句，使用
cursor.execute()
方法，然后使用
cursor.fetchall()
获取所有查询结果。
提交和关闭连接：对于SQLite，使用
conn.commit()
提交事务并使用
conn.close()
关闭连接。对于MySQL，同样使用
conn.commit()
提交事务，但需要使用
conn.close()
关闭连接。

通过这些示例代码，你可以轻松地使用Python连接和操作SQLite和MySQL数据库。务必记住在实际应用中，要处理好异常情况，并采取安全措施，如防止SQL注入等。

5. 数据库连接参数

在连接数据库时，需要提供一些参数以确保正确的连接。对于SQLite，只需提供数据库文件的路径即可。而对于MySQL，除了数据库名称外，还需要提供主机名、用户名和密码等信息。

对于SQLite连接：

sqlite3.connect('example.db')

对于MySQL连接：

conn =mysql.connector.connect(

    host="localhost",

    user="username",

    password="password",

    database="mydatabase")

6. 数据库操作的异常处理

在实际应用中，数据库操作可能会出现各种异常情况，比如连接失败、SQL语法错误等。因此，在进行数据库操作时，务必添加适当的异常处理机制，以提高程序的健壮性和稳定性。

以下是一个简单的异常处理示例：

import sqlite3

import mysql.connectortry:

    # SQLite 连接

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

    cursor_sqlite=conn_sqlite.cursor()



    # MySQL 连接

    conn_mysql=mysql.connector.connect(

        host="localhost",

        user="username",

        password="password",

        database="mydatabase")

    cursor_mysql=conn_mysql.cursor()



    # 进行数据库操作（省略）



except sqlite3.Errorase:

    print("SQLite error:", e)



except mysql.connector.Errorase:

    print("MySQL error:", e)finally:

    # 关闭连接ifconn_sqlite:

        conn_sqlite.close()ifconn_mysql:

        conn_mysql.close()

7. 参数化查询

在执行SQL语句时，尤其是涉及用户输入的情况下，应该使用参数化查询来防止SQL注入攻击。参数化查询可以确保用户输入不会被误解为SQL代码的一部分。

下面是一个使用参数化查询的示例：

import sqlite3

import mysql.connector



# SQLite 连接

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

cursor_sqlite=conn_sqlite.cursor()



# MySQL 连接

conn_mysql=mysql.connector.connect(

    host="localhost",

    user="username",

    password="password",

    database="mydatabase")

cursor_mysql=conn_mysql.cursor()



# 参数化查询

name= "Alice"age= 30# SQLite 参数化查询

cursor_sqlite.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))



# MySQL 参数化查询

sql= "INSERT INTO users (name, age) VALUES (%s, %s)"val=(name, age)

cursor_mysql.execute(sql, val)



# 提交事务并关闭连接

conn_sqlite.commit()

conn_sqlite.close()



conn_mysql.commit()

conn_mysql.close()

8. ORM框架

ORM（Object-Relational Mapping）框架可以将数据库表的行映射为Python对象，简化了数据库操作。在Python中，有许多流行的ORM框架，比如SQLAlchemy、Django的ORM等。这些框架提供了高级的抽象和功能，使得与数据库的交互更加方便和直观。

以下是一个使用SQLAlchemy进行数据库操作的示例：

fromsqlalchemy import create_engine, Column, Integer, Stringfromsqlalchemy.ext.declarative import declarative_basefromsqlalchemy.orm import sessionmaker



# 创建引擎

engine= create_engine('sqlite:///example.db', echo=True)



# 声明基类

Base=declarative_base()



# 定义映射类classUser(Base):

    __tablename__= 'users'id= Column(Integer, primary_key=True)

    name=Column(String)

    age=Column(Integer)



# 创建数据表

Base.metadata.create_all(engine)



# 创建会话

Session= sessionmaker(bind=engine)

session=Session()



# 插入数据

user1= User(name='Alice', age=30)

user2= User(name='Bob', age=25)

session.add(user1)

session.add(user2)

session.commit()



# 查询数据

users=session.query(User).all()for user inusers:

    print(user.id, user.name, user.age)



# 关闭会话

session.close()

9. 使用SQLite内存数据库

除了连接到文件中的SQLite数据库，还可以使用SQLite内存数据库。SQLite内存数据库完全存储在RAM中，对于临时性的数据处理或测试非常方便。

以下是一个使用SQLite内存数据库的示例：

import sqlite3



# 连接到内存数据库

conn= sqlite3.connect(':memory:')



# 创建一个游标对象

cursor=conn.cursor()



# 创建表

cursor.execute('''CREATE TABLE users
                  (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
# 插入数据

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))

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



# 查询数据

cursor.execute("SELECT * FROM users")

rows=cursor.fetchall()for row inrows:

    print(row)



# 提交并关闭连接

conn.commit()

conn.close()

10. 数据库连接池

在高并发的应用中，频繁地打开和关闭数据库连接会消耗大量资源。为了提高性能，可以使用数据库连接池技术，将数据库连接预先创建好并保存在池中，需要时从池中获取连接，使用完毕后归还到池中。

以下是使用
sqlitepool
库实现SQLite数据库连接池的示例：

fromsqlitepool import ConnectionPool



# 创建数据库连接池

pool= ConnectionPool('example.db', max_connections=5)



# 从连接池中获取连接

conn=pool.getconn()



# 创建游标对象

cursor=conn.cursor()



# 执行查询

cursor.execute("SELECT * FROM users")

rows=cursor.fetchall()for row inrows:

    print(row)



# 释放连接回连接池

pool.putconn(conn)

11. 性能优化

在进行大规模数据操作时，需要考虑性能优化。一些常见的性能优化策略包括：

使用索引来加速查询。
合理设计数据库结构，避免过度规范化或反规范化。
批量操作数据，减少数据库交互次数。
缓存查询结果，减少重复查询数据库的次数。

12. 使用异步数据库库

随着异步编程的流行，出现了许多支持异步操作的数据库库，如
aiosqlite
和
aiomysql
。这些库可以与异步框架（如asyncio）结合使用，提高程序的并发性能。

以下是一个使用
aiosqlite
库进行异步SQLite数据库操作的示例：

import asyncio

import aiosqliteasyncdef main():

    # 连接到 SQLite 数据库async with aiosqlite.connect('example.db') asdb:

        # 创建一个游标对象

        cursor= awaitdb.cursor()



        # 创建表await cursor.execute('''CREATE TABLE IF NOT EXISTS users
                               (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
# 插入数据await cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))await cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25))



        # 查询数据await cursor.execute("SELECT * FROM users")

        rows= awaitcursor.fetchall()for row inrows:

            print(row)



# 运行异步主程序

asyncio.run(main())

13. 数据库迁移

在实际项目中，随着需求的变化，可能需要对数据库结构进行修改，这时候就需要进行数据库迁移（Migration）。数据库迁移工具可以帮助我们管理数据库结构变更的过程，并确保数据的一致性。

对于SQLite，可以使用
sqlite3
自带的支持。对于MySQL等数据库，常用的迁移工具包括
Alembic
、
django.db.migrations
等。

以下是一个简单的数据库迁移示例（以SQLite为例）：

import sqlite3



# 连接到 SQLite 数据库

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

cursor=conn.cursor()



# 执行迁移操作（修改表结构）

cursor.execute("ALTER TABLE users ADD COLUMN email TEXT")



# 提交并关闭连接

conn.commit()

conn.close()

14. 备份与恢复

定期备份数据库是保障数据安全的重要措施之一。备份可以通过数据库管理工具或编程方式来实现，具体方法取决于数据库类型和需求。

以下是一个简单的备份数据库的示例（以SQLite为例）：

import shutil



# 备份数据库文件

shutil.copyfile('example.db', 'example_backup.db')

在实际应用中，备份数据库时需要考虑数据库是否处于活动状态、备份文件存储位置、备份周期等因素。

15. 使用环境变量管理数据库连接信息

在实际项目中，将数据库连接信息硬编码在代码中可能不够安全或不够灵活。一种更好的做法是使用环境变量来管理敏感信息，比如数据库的主机名、用户名和密码等。

以下是一个使用环境变量管理数据库连接信息的示例：

import os

import sqlite3

import mysql.connector



# 从环境变量中获取数据库连接信息

DB_HOST= os.getenv('DB_HOST', 'localhost')

DB_USER= os.getenv('DB_USER', 'username')

DB_PASSWORD= os.getenv('DB_PASSWORD', 'password')

DB_NAME= os.getenv('DB_NAME', 'mydatabase')



# SQLite 连接

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

cursor_sqlite=conn_sqlite.cursor()



# MySQL 连接

conn_mysql=mysql.connector.connect(

    host=DB_HOST,

    user=DB_USER,

    password=DB_PASSWORD,

    database=DB_NAME

)

cursor_mysql=conn_mysql.cursor()



# 进行数据库操作（省略）



# 关闭连接

conn_sqlite.close()

conn_mysql.close()

通过使用环境变量，我们可以轻松地在不同的环境中切换数据库连接信息，而无需修改代码。

16. 使用配置文件管理数据库连接信息

除了使用环境变量，还可以使用配置文件来管理数据库连接信息。这种方法更加灵活，可以根据需要配置不同的环境，如开发环境、测试环境和生产环境等。

以下是一个使用配置文件管理数据库连接信息的示例：

import configparser

import sqlite3

import mysql.connector



# 从配置文件中读取数据库连接信息

config=configparser.ConfigParser()

config.read('config.ini')



DB_HOST= config.get('Database', 'host')

DB_USER= config.get('Database', 'user')

DB_PASSWORD= config.get('Database', 'password')

DB_NAME= config.get('Database', 'database')



# SQLite 连接

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

cursor_sqlite=conn_sqlite.cursor()



# MySQL 连接

conn_mysql=mysql.connector.connect(

    host=DB_HOST,

    user=DB_USER,

    password=DB_PASSWORD,

    database=DB_NAME

)

cursor_mysql=conn_mysql.cursor()



# 进行数据库操作（省略）



# 关闭连接

conn_sqlite.close()

conn_mysql.close()

通过配置文件的方式，我们可以将数据库连接信息集中管理，便于维护和修改。

17. 数据库连接的安全性考虑

在连接数据库时，需要考虑安全性问题，特别是涉及到密码和敏感信息的处理。一些常见的安全性措施包括：

不要将敏感信息硬编码在代码中，而是使用环境变量或配置文件管理。
使用加密技术保护敏感信息在传输过程中的安全性。
使用强密码，并定期更换密码。
限制数据库用户的权限，避免赋予过高的权限。

通过采取这些安全性措施，可以有效保护数据库连接信息和数据的安全。

总结

本文介绍了使用Python进行数据库连接与操作的多种方法和技术。首先，我们学习了如何使用Python连接和操作SQLite和MySQL数据库，包括创建表、插入数据、查询数据等基本操作。然后，我们探讨了一些高级技术，如参数化查询、ORM框架、异步数据库库、数据库迁移、备份与恢复等，这些技术可以提高数据库操作的效率和安全性。此外，我们还介绍了如何使用环境变量和配置文件来管理数据库连接信息，以及一些数据库连接的安全性考虑。通过这些技术和方法，我们可以更好地管理和保护数据库，使得数据库编程更加安全、灵活和高效。

在实际项目中，我们需要根据项目需求和安全标准选择合适的技术和工具，确保数据库连接和操作的安全性和可靠性。同时，我们也要不断学习和探索新的技术，以跟上数据库领域的发展和变化。希望本文能够帮助读者更好地理解和应用Python数据库编程的相关知识，为实际项目开发提供帮助和指导。

点击关注，第一时间了解华为云新鲜技术~