🐬 MySQL操作

基本连接和操作

import mysql.connector

# 连接数据库
cnx = mysql.connector.connect(
    host="localhost",
    user="root",
    password="123456",
    database="spider-1"
)

# 创建游标
cursor = cnx.cursor()

# 创建表
create_table_query = """
CREATE TABLE schools (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_name VARCHAR(255),
  belong VARCHAR(255),
  dual_class_name VARCHAR(255))
"""
cursor.execute(create_table_query)

# 插入数据
insert_query = """
INSERT INTO schools (school_name, belong, dual_class_name)
VALUES (%s, %s, %s)
"""
values = ("Peking University", "Ministry of Education", "211,985")
cursor.execute(insert_query, values)
cnx.commit()

# 查询数据
select_query = "SELECT * FROM schools"
cursor.execute(select_query)
for row in cursor:
    print(row)

# 更新数据
update_query = "UPDATE schools SET dual_class_name = %s WHERE id = %s"
values = ("211", 1)
cursor.execute(update_query, values)
cnx.commit()

# 删除数据
delete_query = "DELETE FROM schools WHERE id = %s"
values = (1,)
cursor.execute(delete_query, values)
cnx.commit()

# 关闭游标和连接
cursor.close()
cnx.close()

事务操作

import mysql.connector

cnx = mysql.connector.connect(
    host="localhost",
    user="root",
    password="123456",
    database="spider-1"
)

cursor = cnx.cursor()

insert_query = """
INSERT INTO schools (school_name, belong, dual_class_name)
VALUES (%s, %s, %s)
"""
values1 = ("Peking University", "Ministry of Education", "211,985")
values2 = ("Tsinghua University", "Ministry of Education", "985")
cursor.execute(insert_query, values1)
cursor.execute(insert_query, values2)
cnx.commit()

cursor.close()
cnx.close()

📦 SQLite操作

import sqlite3

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

# 创建游标
cursor = conn.cursor()

# 创建表
create_table_query = """
CREATE TABLE schools (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  school_name TEXT,
  belong TEXT,
  dual_class_name TEXT)
"""
cursor.execute(create_table_query)

# 插入数据
insert_query = """
INSERT INTO schools (school_name, belong, dual_class_name)
VALUES (?, ?, ?)
"""
values = ("Peking University", "Ministry of Education", "211,985")
cursor.execute(insert_query, values)
conn.commit()

# 查询数据
select_query = "SELECT * FROM schools"
cursor.execute(select_query)
for row in cursor:
    print(row)

# 更新数据
update_query = "UPDATE schools SET dual_class_name = ? WHERE id = ?"
values = ("211", 1)
cursor.execute(update_query, values)
conn.commit()

# 删除数据
delete_query = "DELETE FROM schools WHERE id = ?"
values = (1,)
cursor.execute(delete_query, values)
conn.commit()

cursor.close()
conn.close()

🔧 SQLAlchemy ORM

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import Session

# 创建数据库连接
engine = create_engine('mysql+pymysql://user:password@localhost:3306/test', echo=True)

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)

Base.metadata.create_all(engine)

session = Session(engine)

# 插入数据
user = User(name='Tom', age=18)
session.add(user)
session.commit()

# 查询数据
users = session.query(User).all()
for user in users:
    print(user.id, user.name, user.age)

# 更新数据
user = session.query(User).filter_by(name='Tom').first()
user.age = 20
session.commit()

# 删除数据
user = session.query(User).filter_by(name='Tom').first()
session.delete(user)
session.commit()