创建连接
# 参数: '数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')# sqlite 是文件数据库，连接方式不同
# sqlite://<nohostname>/<path>
engine = create_engine('sqlite:///foo.db')# Unix/Mac
engine = create_engine('sqlite:////absolute/path/to/foo.db')# windows
engine = create_engine(r'sqlite:///C:\path\to\foo.db')
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
表的创建
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()class User(Base):
__tablename__ = 'users' id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String) def __repr__(self):
return "<User(name='%s', fullname='%s', password='%s')>" % (
self.name, self.fullname, self.password) # 创建表
Base.metadata.create_all(engine)
CRUD
创建 session
from sqlalchemy.orm import sessionmakerDBSession = sessionmaker(bind=engine)
session = DBSession()
插入数据
# 插入单条记录
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)# 插入多条记录
session.add_all([
User(name='wendy', fullname='Wendy Williams', password='foobar'),
User(name='mary', fullname='Mary Contrary', password='xxg527'),
User(name='fred', fullname='Fred Flinstone', password='blah')])# 提交到数据库
session.commit()# 如果想要操作只是在内存中，不真正commit，ed_user.id 不为None，可以使用flush操作，它只是写到内存中
session.flush()
更新
user.name = 'haha'
session.commit()
删除
session.delete(user)
