SQLAlchemyの增删改查
分类:
IT文章
•
2023-11-28 10:41:13
用a*my写原味sql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
Base = declarative_base()
# 创建单表
class Users(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32))
extra = Column(String(16))
# 数据库连接相关
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day128?charset=utf8")
def init_db():
# 创建表
Base.metadata.create_all(engine)
def drop_db():
# 删除表
# Base.metadata.drop_all(engine)
pass
if __name__ == '__main__':
# drop_db()
init_db()
models
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day128?charset=utf8")
cur = engine.execute('SELECT * FROM users')
result = cur.fetchall()
print(result)
原味sql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, UniqueConstraint, Index, DateTime, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import relationship
import datetime
Base = declarative_base()
class Classes(Base):
__tablename__ = 'classes'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False, unique=True)
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(32), nullable=False, index=True)
password = Column(String(64), nullable=False)
ctime = Column(DateTime, default=datetime.datetime.now)
class_id = Column(Integer, ForeignKey("classes.id"))
# 关联字段 backref = 反向字段
cls = relationship("Classes", backref='stus')
class Hobby(Base):
__tablename__ = 'hobby'
id = Column(Integer, primary_key=True)
caption = Column(String(50), default='篮球')
class Student2Hobby(Base):
__tablename__ = 'student2hobby'
id = Column(Integer, primary_key=True, autoincrement=True)
student_id = Column(Integer, ForeignKey('student.id'))
hobby_id = Column(Integer, ForeignKey('hobby.id'))
__table_args__ = (
UniqueConstraint('student_id', 'hobby_id', name='uix_student_id_hobby_id'),
# Index('ix_id_name', 'name', 'extra'),
)
def init_db():
# 数据库连接相关
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day128?charset=utf8")
# 创建表
Base.metadata.create_all(engine)
def drop_db():
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day128?charset=utf8")
# 删除表
Base.metadata.drop_all(engine)
if __name__ == '__main__':
# drop_db()
init_db()