一、一对一表操作
1、创建数据库连接,并创建相关的数据表
from sqlalchemy import create_engine,Column,String,Integer,ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
global engine
def create_connect(username,password,DB_host,DB_port,DB_name,charset="utf8"):
global engine
data_connect = "mysql+pymysql://%s:%s@%s:%s/%s?charset=%s" % \
(username,password,DB_host,DB_port,DB_name,charset,)
print(data_connect)
engine = create_engine(data_connect,echo=True,pool_size=5,
pool_timeout=30,pool_recycle=-1,max_overflow=5,encoding='utf8')
Base.metadata.create_all(engine)
def loadSession():
Session = sessionmaker(bind=engine)
session = Session()
return session
class Book(Base):
__tablename__ = 'books'
bid = Column(Integer,nullable=False,autoincrement=True,primary_key=True)
title = Column(String(120),nullable=False)
author_id = Column(Integer, ForeignKey('authors.aid'))
class Author(Base):
__tablename__ = 'authors'
aid = Column(Integer,nullable=False,autoincrement=True,primary_key=True)
name = Column(String(64),nullable=False)
if __name__ == '__main__':
create_connect('root','root','127.0.0.1',3306,'flask_sql')
session = loadSession()
2、为各表创建数据
b1 = Book(title='神雕侠侣',author_id=1)
b2 = Book(title='碧血剑',author_id=1)
b3 = Book(title='盗墓笔记',author_id=2)
a1 = Author(name='金庸')
a2 = Author(name='南派三叔')
session.add_all([b1,b2,b3,a1,a2])
session.commit()
3、数据库查询
book = session.query(Book,Author).join(Author,Author.aid == Book.author_id).order_by(Book.bid).all()
print(book)
for books in book:
print(books[0].bid,books[0].title,books[1].aid,books[1].name)
session.close()
二、多对多操作(M2M)
1、创建数据库连接,并创建相关的数据表
from sqlalchemy import create_engine,Column,String,Integer,ForeignKey,UniqueConstraint
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
global engine
def create_connect(username,password,DB_host,DB_port,DB_name,charset="utf8"):
global engine
data_connect = "mysql+pymysql://%s:%s@%s:%s/%s?charset=%s" % \
(username,password,DB_host,DB_port,DB_name,charset,)
print(data_connect)
engine = create_engine(data_connect,echo=True,pool_size=5,
pool_timeout=30,pool_recycle=-1,max_overflow=5,encoding='utf8')
Base.metadata.create_all(engine)
def loadSession():
Session = sessionmaker(bind=engine)
session = Session()
return session
class Author(Base):
__tablename__ = 'authors'
aid = Column(Integer,nullable=False,autoincrement=True,primary_key=True)
name = Column(String(64),nullable=False)
class Book(Base):
__tablename__ = 'books'
bid = Column(Integer,nullable=False,autoincrement=True,primary_key=True)
title = Column(String(120),nullable=False)
authors = relationship('Author',secondary='book2author',backref='books')
class Book2Author(Base):
__tablename__ = 'book2author'
id = Column(Integer,nullable=False,autoincrement=True,primary_key=True)
book_id = Column(Integer,ForeignKey('books.bid'))
author_id = Column(Integer,ForeignKey('authors.aid'))
__table_args__ = (
UniqueConstraint('book_id','author_id',name='uix_book_author'),
)
2、创建数据、获取对应信息
if __name__ == '__main__':
create_connect('root','root','127.0.0.1',3306,'flask_sql')
session = loadSession()
book_author = session.query(Book).filter(Book.bid == 2).first()
print(book_author.authors[0].name)
session.close()
三、执行原生SQL
数据库SQL语句
cursor = session.execute('sql语句')
result = cursor.fetchall()
cursor = session.execute('sql语句')
session.commit()
print(cursor.lastrowid)
session.remove()