Python使用sqlalchemy模块连接数据库操作示例

本文实例讲述了python使用sqlalchemy模块连接数据库操作。分享给大家供大家参考，具体如下：

安装：

pip install sqlalchemy
# 安装数据库驱动：
pip install pymysql
pip install cx_oracle

举例：（在url后面加入?charset=utf8可以防止乱码）

from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://username:password@hostname:port/dbname', echo=true) #echo=true 打印sql语句信息

create_engine接受一个url，格式为：

# '数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
# 常用的
engine = create_engine('sqlite:///:memory:', echo=true)   # sqlite内存
engine = create_engine('sqlite:///./cnblogblog.db',echo=true) # sqlite文件
engine = create_engine("mysql+pymysql://username:password@hostname:port/dbname",echo=true) # mysql+pymysql
engine = create_engine('mssql+pymssql://username:password@hostname:port/dbname',echo=true) # mssql+pymssql
engine = create_engine('postgresql://scott:tiger@hostname:5432/dbname') # postgresql示例
engine = create_engine('oracle://scott:tiger@hostname:1521/sidname') # oracle
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname') #pdb就可以用tns连接

简单demo：

from sqlalchemy import create_engine, column, integer, string
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base 
engine = create_engine('oracle://spark:a@orclpdb',echo=true) #echo要求打印sql语句等调试信息
session_maker = sessionmaker(bind=engine)
session = session_maker()
base = declarative_base()
#对应一张表
class student(base): 
  __tablename__ = 'student'
  id = column('stuid', integer, primary_key=true)
  name = column('stuname', string(32), nullable=false)
  age = column('stuage', integer)
  def __repr__(self):
    return '<student(id:%s, name:%s, age:%s)>' % (self.id, self.name, self.age)
base.metadata.create_all(engine) #若存在student表则不做，不存在则创建。
queryobject = session.query(student).order_by(student.id.desc())
for ins in queryobject:
  print(ins.id, ins.name, ins.age)
'''
4 hey 24
3 lwtxxs 27
2 gyb 89
1 ns 23
'''

将查询结果映射为dataframe：

import pandas as pd
df = pd.read_sql(session.query(student).filter(student.id > 1).statement, engine) 
print(df)
'''
  stuid stuname stuage
0   4   hey   24
1   2   gyb   89
2   3 lwtxxs   27
'''

查询：

session的query方法除了可以接受base子类对象作为参数外，还可以是字段，如：

query = session.query(student.name, student.age) # query为一个sqlalchemy.orm.query.query对象
for stu_name, stu_age in query:
  print(stu_name, stu_age)

查询条件filter：

# = / like
query.filter(student.name == 'wendy')
query.filter(student.name.like('%ed%'))
# in
query.filter(student.name.in_(['wendy', 'jack']))
query.filter(student.name.in_(
    session.query(user.name).filter(user.name.like('%ed%'))
))
# not in
query.filter(~student.name.in_(['ed', 'wendy', 'jack']))
# is null / is not null
query.filter(student.name == none)
query.filter(student.name.is_(none))
query.filter(student.name != none)
query.filter(student.name.isnot(none))
# and
from sqlalchemy import and_, or_
query.filter(and_(student.name == 'ed', student.age != 23))
query.filter(student.name == 'ed', student.age != 23)
query.filter(student.name == 'ed').filter(student.age != 23)
# or
query.filter(or_(student.name == 'ed', student.name == 'wendy'))
# match
query.filter(student.name.match('wendy'))

query的方法：

all()方法以列表形式返回结果集：

from sqlalchemy import or_, and_
queryobject = session.query(student).filter(or_(student.id == 1, student.id == 2))
print(queryobject.all())  # [<student(id:1, name:ns, age:23)>, <student(id:2, name:gyb, age:89)>]
queryobject = session.query(student.name).filter(or_(student.id == 1, student.id == 2))
print(queryobject.all())  # [('ns',), ('gyb',)]

first()方法返回单个结果。（若结果集为空则返回none）

print(queryobject.first()) 　#　('ns',)

one()方法返回单个结果，与first()方法不同的是：当结果集中没有元素或有多于一个元素会抛出异常。
one_or_none()方法同one()一样，不同是结果集为空则返回none，为多个抛出异常。

查询数量：

from sqlalchemy import func
session.query(func.count(student.id)).scalar()　# select count("student"."stuid") as count_1 from "student"

分组：

session.query(func.count(student.id), student.name).group_by(student.name).all()

嵌套sql语句：

from sqlalchemy import text
query = session.query(student.id, student.name).filter(text('stuid>2'))
query = session.query('stuid', 'stuname', 'stuage').from_statement(\
text("select * from student where stuname=:stuname")).params(stuname='hey').all()  #[(4, 'hey', 24)]

更多关于python相关内容感兴趣的读者可查看本站专题：《python常见数据库操作技巧汇总》、《python数学运算技巧总结》、《python数据结构与算法教程》、《python函数使用技巧总结》、《python字符串操作技巧汇总》、《python入门与进阶经典教程》及《python文件与目录操作技巧汇总》

希望本文所述对大家python程序设计有所帮助。