sqlalchemy

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

结果：