sqlalchemy快速使用
安装: SQLAlchemy
Engine 框架的引擎
Connection Pooling 数据连接池
Dialect 选择链接数据库的DB API种类:mysql sqllite等等
Schema/Types 架构和类型
SQL Exprression Language SQL表达式语言
pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
eg:
mysql+pymysql://root:123@127.0.0.1:3306/movie
MySQL-Connector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
操作原生sql快速使用
from threading import Thread
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine
engin = create_engine(
'mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8',
max_overflow=0,
pool_size=5,
pool_timeout=30,
pool_recycle=-1
)
def task():
conn = engin.raw_connection()
cursor = conn.cursor()
cursor.execute("select * from boy")
print(cursor.fetchall())
for i in range(20):
t = Thread(target=task)
t.start()
使用sqlalchemy创建表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
import datetime
from sqlalchemy import create_engine
Base = declarative_base()
class User(Base):
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=False)
email = Column(String(32), unique=True)
ctime = Column(DateTime, default=datetime.datetime.now)
extra = Column(Text, nullable=True)
__tablename__ = 'users'
__table_args__ = (
UniqueConstraint('id', 'name', name='uix_id_name'),
Index('ix_id_name', 'name', 'email')
)
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=False)
price = Column(Integer)
class Publish(Base):
__tablename__ = 'publish'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=True)
def init_db():
engine = create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8",
max_overflow=0,
pool_size=5,
pool_timeout=30,
pool_recycle=-1
)
Base.metadata.create_all(engine)
def drop_db():
engine = create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8",
max_overflow=0,
pool_size=5,
pool_timeout=30,
pool_recycle=-1
)
Base.metadata.drop_all(engine)
if __name__ == '__main__':
init_db()
使用sqlalchemy插入数据【使用线程安全】
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
from models import User, Book, Publish
from sqlalchemy.orm import scoped_session
session = scoped_session(Session)
def task(i):
user = User(name='summer%s'%i, email='%s@qq.com'%i, extra='好冷')
session.add(user)
session.commit()
session.close()
from threading import Thread
for i in range(10):
t = Thread(target=task,args=[i,])
t.start()
表模型创建【一对多和多对多表关系】
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
class Hobby(Base):
__tablename__ = 'hobby'
id = Column(Integer, primary_key=True)
caption = Column(String(50), default='篮球')
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=True)
hobby_id = Column(Integer, ForeignKey('hobby.id'))
hobby = relationship('Hobby', backref='pers')
class Rule(Base):
__tablename__ = 'rule'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=True,unique=True)
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=True,unique=True)
rules = relationship('Rule',secondary='rule2user',backref='users')
def __str__(self):
return self.name
def __repr__(self):
return self.name
class Rule2User(Base):
__tablename__ = 'rule2user'
id = Column(Integer,primary_key=True)
rule_id = Column(Integer,ForeignKey('rule.id'))
user_id = Column(Integer,ForeignKey('user.id'))
def init_db():
engine = create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8",
max_overflow=0,
pool_size=5,
pool_timeout=30,
pool_recycle=-1
)
Base.metadata.create_all(engine)
if __name__ == '__main__':
init_db()
一对多数据操作
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from model import Hobby,Person
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
hobby = Hobby(caption='羽毛球')
print(hobby.id)
session.add(hobby)
person = Person(name='summer',hobby_id=1)
person = Person(name='jerry',hobby=Hobby(caption='足球'))
hobby = session.query(Hobby).filter_by(id=1).first()
person = Person(name='tony',hobby_id=hobby.id)
session.add(person)
person = session.query(Person).filter_by(id=1).first()
print(person.hobby_id)
print(person.hobby.caption)
hobby = session.query(Hobby).filter_by(id=1).first()
print(hobby.pers)
for person in hobby.pers:
print(person.name)
session.commit()
session.close()
多对多数据操作
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from model import User, Rule2User, Rule
from models import Book
from sqlalchemy.sql import text
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
session.commit()
session.close()
session操作原生sql
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from models import User
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
cursor = session.execute('insert into User(name) values(:value)',params={'value':'ssss'})
print(cursor.lastrowid)
session.commit()
session.close()
flask中使用sqlalchemy
1 导入 from flask_sqlalchemy import SQLAlchemy
2 实例化得到对象
db = SQLAlchemy()
3 将db注册到app中
db.init_app(app)
4 视图函数中使用session
全局的db.session
5 models.py 中继承Base
db.Base
6 写字段
username = db.Column(db.String(80), unique=True, nullable=False)
flask-migrate
1 导入
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
2 注册
manager = Manager(app)
Migrate(app, db)
3 给flask_script增加一个db命令
manager.add_command('db', MigrateCommand)
4 出现3条命令
python manage.py db init
python manage.py db migrate
python manage.py db upgrade
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战