(五)python实战——使用sqlalchemy完成Sqlite3数据库表的增、删、查、改操作案例_sqlalchemy sqlite3-CSDN博客
ORM Events — SQLAlchemy 2.0 Documentation使用说明文档
采用游标方式SQLite – Python | 菜鸟教程
有SQLAlchemy 之 create_engine和session-CSDN博客层
#数据库连接
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
self.engine = create_engine(conn['conn_str'],
pool_size=16,
pool_pre_ping=True)
self.session = sessionmaker(bind=self.engine)
conn = '数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
#连接池
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from models import Student,Course,Student2Course
engine = create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置)
)
SessionFactory = sessionmaker(bind=engine)
session = scoped_session(SessionFactory)
def task():
""""""
# 方式一:
"""
# 查询
# cursor = session.execute('select * from users')
# result = cursor.fetchall()
# 添加
cursor = session.execute('INSERT INTO users(name) VALUES(:value)', params={"value": 'wupeiqi'})
session.commit()
print(cursor.lastrowid)
"""
# 方式二:
"""
# conn = engine.raw_connection()
# cursor = conn.cursor()
# cursor.execute(
# "select * from t1"
# )
# result = cursor.fetchall()
# cursor.close()
# conn.close()
"""
# 将连接交还给连接池
session.remove()
from threading import Thread
for i in range(20):
t = Thread(target=task)
t.start()
#session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()
sqlalchemy触发器的使用-Event_sqlalchemy 事件-CSDN博客
#coding:utf8
from sqlalchemy.orm import scoped_session
from sqlalchemy import Column, Integer, String, DateTime, TIMESTAMP, DECIMAL, func, Text, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import ForeignKey, Boolean, create_engine, MetaData, Constraint
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy import event
Base = declarative_base()
class Role(Base):# 一
__tablename__= 'roles'
id = Column(Integer,primary_key=True)
name = Column(String(36),nullable=True)
users = relationship('User',backref='role')
class User(Base):# 多
__tablename__ = 'users'
id = Column(Integer,primary_key=True)
name = Column(String(36),nullable=True)
role_id = Column(Integer, ForeignKey('roles.id'))
class Database():
def __init__(self, bind, pool_size=100, pool_recycle=3600, echo=False):
self.__engine = create_engine(bind,pool_size=pool_size,
pool_recycle=pool_recycle,
echo=echo)
self.__session_factory = sessionmaker(bind=self.__engine)
self.__db_session = scoped_session(self.__session_factory)
Base.metadata.create_all(self.__engine)
@property
def session(self):
return self.__db_session()
def on_created(target, value, initiator):
print "received append event for target: %s" % target
@event.listens_for(User, 'after_insert')
def receive_after_insert(mapper, connection, target):
print mapper
print connection
print target.id
print "insert......."
db = Database(bind="mysql+pymysql://root:xxxx@localhost/mydata?charset=utf8")
if __name__ == "__main__":
user = User()
user.name = "123"
user.role_id=2
db.session.add(user)
db.session.commit()
# 引入sqlalchemy依赖
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
# 申明基类对象
Base = declarative_base()
# 定义user表实体对象
class User(Base):
# 定义表名
__tablename__ = 'user'
# 定义字段
id = Column(Integer, primary_key=True)
name = Column(String(255))
age = Column(Integer)
def __repr__(self):
return "User(id:{},name:{},age:{})".format(self.id, self.name, self.age)
(五)python实战——使用sqlalchemy完成Sqlite3数据库表的增、删、查、改操作案例_sqlalchemy sqlite3-CSDN博客
【Flask】创建ORM映射以及参数解释_declarative_base()-CSDN博客
# 引入sqlalchemy依赖
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
# 申明基类对象
Base = declarative_base()
# sqlite连接初始化
class SqliteSqlalchemy(object):
def __init__(self):
# 创建sqlite连接引擎
engine = create_engine('sqlite:///./sqlalchemy.db', echo=True)
# 创建表
Base.metadata.create_all(engine, checkfirst=True)
# 创建sqlite的session连接对象
self.session = sessionmaker(bind=engine)()
【Flask】创建ORM映射以及参数解释_declarative_base()-CSDN博客
#创建一个ORM模型,说明基于sqlalchemy 映射到mysql数据库的常用字段类型
Base=declarative_base(engine)
#创建模型类
class News(Base):
__tablename__='t_news'
id=Column(type_=Integer,primary_key=True,autoincrement=True)
price1=Column(type_=Float) #存储数据时存在精度丢失问题
price2=Column(type_=DECIMAL(10,4))
title=Column(type_=String(50))
is_delete=Column(type_=Boolean)
tag1=Column(Enum('PYTHON','FLASK','DJANGO')) #枚举常规写法
tag2=Column(Enum(TagEnum)) #枚举的另一种写法,但是需要定义一个类,类中定义一些需要的属性
create_time1=Column(type_=Date)
create_time2=Column(type_=DateTime)
create_time3=Column(type_=Time)
content1=Column(type_=Text)
content2=Column(type_=LONGTEXT)
#根据模型类生成数据表
Base.metadata.create_all()
增加
# 引入sqlalchemy依赖
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
# 申明基类对象
Base = declarative_base()
# 定义user表实体对象
class User(Base):
# 定义表名
__tablename__ = 'user'
# 定义字段
id = Column(Integer, primary_key=True)
name = Column(String(255))
age = Column(Integer)
def __repr__(self):
return "User(id:{},name:{},age:{})".format(self.id, self.name, self.age)
class SqliteSqlalchemy(object):
def __init__(self):
# 创建Sqlite连接引擎
engine = create_engine('sqlite:///./sqlalchemy.db', echo=True)
# 创建表
Base.metadata.create_all(engine, checkfirst=True)
# 创建Sqlite的session连接对象
self.session = sessionmaker(bind=engine)()
if __name__ == '__main__':
# 初始化Sqlite数据库连接,获取数据库session连接
session = SqliteSqlalchemy().session
# 新增一条用户信息数据
user = User(name='xiaoming', age=23)
session.add(user)
session.commit()
# 关闭数据库session连接
session.close()
#增加多条信息
if __name__ == '__main__':
# 初始化Sqlite数据库连接,获取数据库session连接
session = SqliteSqlalchemy().session
# 新增多条用户信息数据
datas = [
User(name='张三', age=20),
User(name='李四', age=21),
User(name='王五', age=22),
]
session.add_all(datas)
session.commit()
# 关闭数据库session连接
session.close()
删除
if __name__ == '__main__':
# 初始化Sqlite数据库连接,获取数据库session连接
session = SqliteSqlalchemy().session
# 根据id删除数据
user_id = 1
del_count = session.query(User).filter(User.id == user_id).delete()
print('删除数目:', del_count)
session.commit()
# 关闭数据库session连接
session.close()
修改
if __name__ == '__main__':
# 初始化Sqlite数据库连接,获取数据库session连接
session = SqliteSqlalchemy().session
# 根据ID更新用户数据
user_id = 1
session.query(User).filter(User.id == user_id).update({User.name: "王五", User.age: 18})
session.commit()
# 关闭数据库session连接
session.close()
查询
if __name__ == '__main__':
# 初始化Sqlite数据库连接,获取数据库session连接
session = SqliteSqlalchemy().session
# 查询user用户表中的全部数据
result = session.query(User.id, User.name, User.age).all()
print(result)
# 关闭数据库session连接
session.close()
增删改查出处:(五)python实战——使用sqlalchemy完成Sqlite3数据库表的增、删、查、改操作案例_sqlalchemy sqlite3-CSDN博客