如何用Python连接数据库？SQLite和MySQL操作指南！-Python教程-PHP中文网

python连接数据库可通过特定库实现，sqlite适合小型应用，mysql适用于中大型应用。1.使用sqlite3模块可连接sqlite数据库，通过connect()方法创建连接，cursor()创建游标执行sql语句；2.创建表时用create table if not exists确保表不存在时才创建，主键设定与字段非空约束需明确；3.插入数据使用?占位符防止sql注入，并调用commit()提交事务；4.查询数据通过select语句配合fetchall()获取结果；5.mysql连接需安装mysql-connector-python，使用connect()并传入host、user、password、database参数；6.处理连接错误可用try...except块捕获异常，并根据错误码做针对性处理；7.orm框架如sqlalchemy可简化操作，通过定义类映射表结构，使用session管理增删改查；8.连接池管理提升性能，mysql支持内置连接池，sqlite可通过自定义或第三方库实现；9.防止sql注入应使用参数化查询、输入验证、最小权限原则及定期安全审计。

如何用Python连接数据库？SQLite和MySQL操作指南！

连接数据库，简单来说就是用Python代码和数据库建立起“对话”的桥梁，这样你才能用代码去读取、写入、修改数据库里的数据。

掌握Python连接数据库的方法，无论是SQLite还是MySQL，对于数据处理至关重要。下面将介绍如何操作。

SQLite数据库连接与操作

SQLite是轻量级的嵌入式数据库，无需单独安装服务器，非常适合小型应用和学习。

立即学习“Python免费学习笔记（深入）”；

连接SQLite数据库

import sqlite3

# 连接到数据库 (如果数据库不存在，则会自动创建)
conn = sqlite3.connect('mydatabase.db')

# 创建一个cursor对象，用于执行SQL语句
cursor = conn.cursor()

登录后复制

这里，sqlite3.connect('mydatabase.db') 创建了一个到名为 mydatabase.db 的SQLite数据库的连接。如果该文件不存在，Python 会自动创建一个。cursor = conn.cursor() 创建了一个游标对象，你可以通过这个游标来执行SQL命令。

创建表

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

登录后复制

cursor.execute() 用于执行SQL语句。这段代码创建了一个名为 users 的表，包含 id、name 和 age 三个字段。INTEGER PRIMARY KEY 表示 id 是主键，TEXT NOT NULL 表示 name 字段不能为空。IF NOT EXISTS 确保只有当表不存在时才创建。

插入数据

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25))
conn.commit() # 提交事务，保存更改

登录后复制

这段代码插入了两条数据到 users 表中。? 是占位符，用于安全地传递参数，防止SQL注入。conn.commit() 用于提交事务，将更改保存到数据库。如果不提交，数据不会真正写入。

查询数据

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

登录后复制

这段代码查询了 users 表中的所有数据，并通过循环打印每一行。cursor.fetchall() 获取所有查询结果，返回一个列表，其中每个元素都是一个元组，代表一行数据。

关闭连接

conn.close()

登录后复制

操作完成后，务必关闭数据库连接，释放资源。

MySQL数据库连接与操作

MySQL是流行的关系型数据库，适用于中大型应用。

安装MySQL Connector/Python

首先，需要安装MySQL Connector/Python，可以使用pip：

pip install mysql-connector-python

登录后复制

连接MySQL数据库

import mysql.connector

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

mycursor = mydb.cursor()

登录后复制

你需要替换 yourusername、yourpassword 和 yourdatabase 为你的MySQL用户名、密码和数据库名。mysql.connector.connect() 创建了一个到MySQL数据库的连接。

创建表

mycursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

登录后复制

与SQLite类似，这段代码创建了一个名为 customers 的表。AUTO_INCREMENT 使 id 字段自动递增。

插入数据

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

登录后复制

这段代码插入了一条数据到 customers 表中。%s 是占位符，用于安全地传递参数。mycursor.rowcount 返回受影响的行数。

查询数据

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

登录后复制

这段代码查询了 customers 表中的所有数据，并通过循环打印每一行。

关闭连接

mydb.close()

登录后复制

同样，操作完成后，务必关闭数据库连接。

如何处理数据库连接错误？

数据库连接过程中可能会遇到各种错误，例如用户名密码错误、数据库服务器未启动等。处理这些错误至关重要，以确保程序的健壮性。

使用try...except块

可以使用 try...except 块来捕获并处理数据库连接错误。

import sqlite3

try:
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()
    # 其他数据库操作...
    conn.close()
except sqlite3.Error as e:
    print(f"数据库连接错误: {e}")

登录后复制

这段代码尝试连接到SQLite数据库，如果发生任何 sqlite3.Error 异常，程序会捕获该异常并打印错误信息，而不会崩溃。

针对不同错误类型进行处理

可以根据不同的错误类型进行不同的处理。例如，可以检查数据库文件是否存在，或者检查MySQL服务器是否正在运行。

import mysql.connector

try:
    mydb = mysql.connector.connect(
      host="localhost",
      user="yourusername",
      password="yourpassword",
      database="yourdatabase"
    )
    mycursor = mydb.cursor()
    # 其他数据库操作...
    mydb.close()
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("用户名或密码错误")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("数据库不存在")
    else:
        print(err)

登录后复制

这段代码针对MySQL连接错误进行了更细致的处理。如果用户名或密码错误，或者数据库不存在，程序会打印相应的错误信息。

如何使用ORM（对象关系映射）简化数据库操作？

ORM（Object-Relational Mapping）是一种编程技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。简单来说，ORM可以将数据库中的表映射为Python中的类，将表中的行映射为类的实例，从而可以使用面向对象的方式操作数据库。

SQLAlchemy

SQLAlchemy是流行的Python ORM库，提供了强大的数据库操作功能。

安装SQLAlchemy

pip install 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(255))
    age = Column(Integer)

    def __repr__(self):
        return f"<User(name='{self.name}', age='{self.age}')>"

登录后复制

这段代码定义了一个 User 类，它映射到数据库中的 users 表。id、name 和 age 分别映射到表中的字段。

创建数据库和表

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

登录后复制

这段代码创建了一个SQLite数据库，并根据模型创建了相应的表。

创建会话

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

登录后复制

会话用于管理数据库操作。

插入数据

new_user = User(name='Charlie', age=35)
session.add(new_user)
session.commit()

登录后复制

这段代码创建了一个 User 类的实例，并将其添加到数据库中。

查询数据

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

登录后复制

这段代码查询了 users 表中的所有数据，并通过循环打印每个用户的信息。

关闭会话

session.close()

登录后复制

操作完成后，务必关闭会话。

如何进行数据库连接池管理？

数据库连接池是一种管理数据库连接的技术，它可以预先创建一组数据库连接，并将这些连接保存在一个“池”中。当应用程序需要访问数据库时，它可以从连接池中获取一个连接，使用完毕后将连接返回到池中，而不是每次都创建新的连接。这样可以显著提高数据库访问的性能，并减少资源消耗。

使用sqlite3.Pool (适用于SQLite)

虽然sqlite3 模块本身没有内置连接池，但可以使用第三方库或自定义实现。

# 一个简单的SQLite连接池示例 (非线程安全)
import sqlite3

class SQLiteConnectionPool:
    def __init__(self, db_path, max_connections=5):
        self.db_path = db_path
        self.max_connections = max_connections
        self.pool = []
        self.lock = threading.Lock()  # 用于线程安全

    def get_connection(self):
        with self.lock:
            if self.pool:
                return self.pool.pop()
            if len(self.pool) < self.max_connections:
                return sqlite3.connect(self.db_path)
            else:
                raise Exception("连接池已满")

    def release_connection(self, conn):
        with self.lock:
            if len(self.pool) < self.max_connections:
                self.pool.append(conn)
            else:
                conn.close() # 关闭多余的连接

登录后复制

使用mysql.connector.pooling (适用于MySQL)

MySQL Connector/Python 提供了连接池模块。

import mysql.connector
from mysql.connector import pooling

# 创建连接池
dbconfig = {
  "host": "localhost",
  "user": "yourusername",
  "password": "yourpassword",
  "database": "yourdatabase"
}

pool = pooling.MySQLConnectionPool(pool_name = "mypool",
                                       pool_size = 5,
                                       pool_reset_session = True,
                                       **dbconfig)

# 从连接池获取连接
conn = pool.get_connection()
cursor = conn.cursor()

# 执行数据库操作...
cursor.execute("SELECT * FROM customers")
myresult = cursor.fetchall()

for x in myresult:
  print(x)

# 释放连接
cursor.close()
conn.close()

登录后复制

pooling.MySQLConnectionPool() 创建了一个连接池，pool_size 指定了连接池的大小。pool.get_connection() 从连接池获取一个连接。使用完毕后，调用 conn.close() 将连接返回到连接池。

如何防止SQL注入攻击？

SQL注入是一种常见的网络安全漏洞，攻击者通过在应用程序的输入字段中插入恶意的SQL代码，从而绕过应用程序的安全检查，直接访问或修改数据库中的数据。

使用参数化查询或预编译语句

参数化查询或预编译语句是防止SQL注入有效的手段。它们将SQL语句和参数分开处理，确保用户输入的数据不会被解释为SQL代码。

# SQLite
cursor.execute("SELECT * FROM users WHERE name = ? AND age = ?", (username, age))

# MySQL
sql = "SELECT * FROM customers WHERE name = %s AND address = %s"
val = (username, address)
mycursor.execute(sql, val)

登录后复制

在这两种情况下，? 和 %s 都是占位符，用于安全地传递参数。数据库驱动程序会自动对参数进行转义，防止SQL注入。

输入验证和过滤

对用户输入的数据进行验证和过滤，可以有效防止恶意SQL代码的注入。