安装
sudo pip install sqlalchemy或sudo pip3 install sqlalchemy
下载速度慢可使用国内源,如:
sudo pip install sqlalchemy -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com
测试1
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
eng = create_engine('mysql+mysqlconnector://y-user:y-passwd@127.0.0.1:3306/y-db?charset=utf8')
#eng = create_engine('postgresql+psycopg2://y-user:y-passwd@127.0.0.1:5432/y-db',echo=True,client_encoding='utf8')
DB_Session = sessionmaker(bind=eng)
session = DB_Session()
data = session.execute("SELECT * FROM y-table")
for row in data:
for col in row:
print col,
session.close()
测试2
#coding=utf-8
from sqlalchemy import *
from sqlalchemy.orm import *
eng = create_engine('mysql+mysqlconnector://y-user:y-passwd@127.0.0.1:3306/y-db?charset=utf8')
metadata = MetaData(eng) # 绑定元信息
y_table = Table('y-table', metadata, autoload=True)
session = create_session()
query = session.query(y_table)
data = query.all()
for row in data:
for col in row:
print col,
session.close()
测试3--orm方式
数据库表STUDENT的建立参考:
http://my.oschina.net/u/2245781/blog/653897?fromerr=YEVmVLAx
# coding=utf-8
from sqlalchemy import Column, String, create_engine
from sqlalchemy.dialects.mysql import DATETIME
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )
# 创建对象的基类:
Base = declarative_base()
# 定义User对象:
class Stu(Base):
# 表的名字:
__tablename__ = 'STUDENT'
# 表的结构:
SNO = Column(String(7), primary_key=True)
SNAME = Column(String(8))
SEX = Column(String(2))
BDATE = Column(DATETIME)
DIR = Column(String(16))
def __repr__(self):
return "" % \
(self.SNO,self.SNAME,self.SEX,self.BDATE.strftime("%Y-%m-%d"),self.DIR)
# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://y-user:y-passwd@127.0.0.1:3306/y-db?charset=utf8')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
# 创建session对象:
session = DBSession()
query = session.query(Stu)
for s in session.query(Stu):
print(s)
#for s in query.all():
# print s.SNAME+' '+s.SNO+' '+s.SEX +' '+s.BDATE.strftime("%Y-%m-%d")+' ' + s.DIR
session.close()
插入
# coding=utf-8
from sqlalchemy import Column, String, create_engine
from sqlalchemy.dialects.mysql import DATETIME
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base