SQLAlchemy模块

1、执行原生SQL

from sqlalchemy import create_engine, text

# 创建engine对象
engine = create_engine("sqlite:///demo.db", echo=False)
with engine.connect() as con:
    # 先删除persons表
    con.execute(text('drop table if exists persons'))
    # 创建一个persons表，有自增长的id和name,age
    con.execute(text(
        "CREATE TABLE persons (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name VARCHAR (32),age REAL,datetime TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S+08:00','now','localtime')))"))
    # 插入两条数据到表中
    con.execute(text('insert into persons(name,age) values("张三","20")'))
    con.execute(text('insert into persons(name,age) values("李四","30")'))
    # 执行查询操作
    results = con.execute(text('select * from persons'))
    # 获取结果
    # datares = results.fetchall()
    # 将结果转换为字典格式
    data = [dict(zip(results.keys(), result)) for result in results.fetchall()]
    print(data)

 2、ORM

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

# 连接SQLite数据库
engine = create_engine('sqlite:///demo.db', echo=False)

# 创建基类
Base = declarative_base()


# 创建表
class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    title = Column(String)
    author = Column(String)
    year = Column(Integer)

    def __repr__(self):
        return f"<Book(id='{self.id}',title='{self.title}', author='{self.author}', year={self.year})>"


# 创建表
Base.metadata.create_all(engine)

# 创建Session
Session = sessionmaker(bind=engine)
session = Session()

# 插入数据
book1 = Book(title='The Great Gatsby', author='F. Scott Fitzgerald', year=1925)
book2 = Book(title='To Kill a Mockingbird', author='Harper Lee', year=1960)
book3 = Book(title='1984', author='George Orwell', year=1949)
session.add(book1)
session.add(book2)
session.add(book3)
session.commit()
session.close()


# 将查询结果转换为字典格式
def to_dict(self):
    return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}


Base.to_dict = to_dict

# 查询数据
book = session.query(Book).first()
print(book.to_dict())
books = session.query(Book).all()
session.close()
bookdict = [i.to_dict() for i in books]
print(bookdict)