SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
MySQL-Python
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
MySQL-Connector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html
SQLAlchemy---操作数据库实例:https://blog.csdn.net/will130/article/details/48442699
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, DATE
Base = declarative_base() #生成orm基类
class Staff(Base):
__tablename__ = 'Staff' #表名
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(20))
dept = Column(String(20))
salary = Column(Integer)
edlevel = Column(Integer)
hiredate = Column(DATE)
engine = create_engine('mysql+pymysql://alex:123456@192.168.181.128:3306/testdb')
# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine) #创建与数据库的会话session class
session = DBSession()
data1 = Staff(name='a', dept='k', salary=2000, edlevel=3, hiredate='2009-10-11')
data2 = Staff(name='b', dept='k', salary=2500, edlevel=3, hiredate='2009-10-01')
session.add(data1)
session.add(data2)
session.commit()
session.close()
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, DATE
Base = declarative_base() #生成orm基类
class Staff(Base):
__tablename__ = 'Staff' #表名
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(20))
dept = Column(String(20))
salary = Column(Integer)
edlevel = Column(Integer)
hiredate = Column(DATE)
engine = create_engine('mysql+pymysql://alex:123456@192.168.181.128:3306/testdb')
DBSession = sessionmaker(bind=engine)
session = DBSession()
"""
###增
u = Staff(name='jinbo', dept='it', salary=2500, edlevel=3, hiredate='2018-2-2')
session.add(u)
session.add_all([Staff(name='jack', dept='it',salary=2000, edlevel=2, hiredate='2017-2-3'),
Staff(name='mart', dept='market', salary=2000, edlevel=2, hiredate='2017-2-3')])
session.commit()
###删除
d = session.query(Staff).filter(Staff.id==2)
d.delete()
session.commit()
###修改
d2 = session.query(Staff).filter(Staff.name=='a').first()
d2.name = "ethan"
session.commit()
"""
###查
ret = session.query(Staff).filter(Staff.id>2).first()
print(type(ret), ret) ##type:class
ret2 = session.query(Staff).filter(Staff.id>2).all()
print(type(ret2), ret2) ##type:list
d3 = session.query(Staff.name,Staff.id).all()
print(type(d3), d3) ##type:list
结果：
