数据库连接轻松搞定：Python 数据库推荐

1
import sqlite3
2

3
## 连接数据库
4
conn = sqlite3.connect("mydatabase.db")
5

6
## 创建表格
7
cursor = conn.cursor()
8
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
9

10
## 插入数据
11
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
12
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 30))
13

14
## 查询数据
15
cursor.execute("SELECT * FROM users")
16
rows = cursor.fetchall()
17
for row in rows:
18
    print(row)
19

20
## 关闭连接
21
conn.close()

1
import mysql.connector
2

3
## 连接数据库
4
conn = mysql.connector.connect(
5
    host="localhost",
6
    user="root",
7
    password="your_password",
8
    database="mydatabase"
9
)
10

11
## 创建表格
12
cursor = conn.cursor()
13
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)")
14

15
## 插入数据
16
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Alice", 25))
17
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Bob", 30))
18

19
## 查询数据
20
cursor.execute("SELECT * FROM users")
21
rows = cursor.fetchall()
22
for row in rows:
23
    print(row)
24

25
## 关闭连接
26
conn.close()

1
from sqlalchemy import create_engine, Column, Integer, String
2
from sqlalchemy.ext.declarative import declarative_base
3
from sqlalchemy.orm import sessionmaker
4

5
## 连接数据库
6
engine = create_engine("sqlite:///mydatabase.db", echo=True)
7
Base = declarative_base()
8

9
## 定义数据表模型
10
class User(Base):
11
    __tablename__ = "users"
12
    id = Column(Integer, primary_key=True)
13
    name = Column(String)
14
    age = Column(Integer)
15

16
## 创建数据表
17
Base.metadata.create_all(engine)
18

19
## 创建会话
20
Session = sessionmaker(bind=engine)
21
session = Session()
22

23
## 插入数据
24
user1 = User(name="Alice", age=25)
25
user2 = User(name="Bob", age=30)
26
session.add_all([user1, user2])
27
session.commit()
28

29
## 查询数据
30
users = session.query(User).all()
31
for user in users:
32
    print(user.name, user.age)
33

34
## 关闭会话
35
session.close()

1
import csv
2

3
## 连接数据库（使用 SQLite3 示例）
4
conn = sqlite3.connect("mydatabase.db")
5
cursor = conn.cursor()
6

7
## 创建表格
8
cursor.execute("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT, author TEXT)")
9

10
## 从 CSV 文件中读取数据
11
with open("books.csv", "r") as file:
12
    reader = csv.reader(file)
13
    next(reader)  ## 跳过标题行
14
    for row in reader:
15
        title, author = row
16
        cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", (title, author))
17

18
## 查询数据
19
cursor.execute("SELECT * FROM books")
20
rows = cursor.fetchall()
21
for row in rows:
22
    print(row)
23

24
## 关闭连接
25
conn.close()