python2.7连接sqlserver数据库(Python使用sqlalchemy模块连接数据库操作示例)
类别:脚本大全 浏览量:869
时间:2021-11-05 14:49:24 python2.7连接sqlserver数据库
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程序设计有所帮助。
原文链接:https://blog.csdn.net/xuejianbest/article/details/85159552
您可能感兴趣
- python正则表达式入门(Python正则表达式实现简易计算器功能示例)
- pythonredis使用场景(python 通过SSHTunnelForwarder隧道连接redis的方法)
- python递归深度遍历多叉树(Python实现二叉树的常见遍历操作总结7种方法)
- python变量指向讲解(详解python中init方法和随机数方法)
- python爬取微博登录数据(Python实现爬取马云的微博功能示例)
- python udp通信(Python socket模块实现的udp通信功能示例)
- python编写自动发送微信信息(python实现向微信用户发送每日一句 python实现微信聊天机器人)
- python编写的小程序(几个适合python初学者的简单小程序,看完受益匪浅!推荐)
- python3.7对象检测(在Python中使用Neo4j的方法)
- python正则表达式处理教学(使用Python正则表达式操作文本数据的方法)
- opencv抠出边缘检测的图形(python opencv实现图像边缘检测)
- python常用的字符串操作方法(Python字符串的常见操作实例小结)
- 怎么用python做随机矩阵(python实现杨氏矩阵查找)
- python中tkinter模块窗口操作(详解python tkinter教程-事件绑定)
- python什么是深拷贝什么是浅拷贝(Python深拷贝与浅拷贝用法实例分析)
- python列表怎么赋值(详解Python列表赋值复制深拷贝及5种浅拷贝)
- 《极限挑战》深访都市夜归人,夜间打工者体验,黄磊录完憔悴了(极限挑战深访都市夜归人)
- Google 推出了一个游戏生成器,让不会编程的你也能自己设计游戏(推出了一个游戏生成器)
- 二胎家庭老大爱闹情绪,用这招很有效(二胎家庭老大爱闹情绪)
- 一个30岁男人外遇失败的全过程(一个30岁男人外遇失败的全过程)
- 《无敌破坏王2》 不聊彩蛋,聊聊我从动画里看到的现实那些事儿(无敌破坏王2不聊彩蛋)
- 《寄生虫》 三观不正 人类悲欢从来不相通,感同身受也并非本能(寄生虫三观不正)
热门推荐
- sql行转列的应用(SQL查询语句行转列横向显示实例解析)
- 阿里云ecs属于哪种云服务(阿里的轻云服务器、虚拟主机、云服务器ECS的区别?)
- 如何对python中列表中的数据运算(Python3.5集合及其常见运算实例详解)
- css什么时候可以用百分比(浅谈CSS中的百分比)
- 异步调用中HttpContext.Current为null解决方法
- 快速建站指南(西部数码建站助手使用图文教程)
- vnc连接linux黑屏(阿里云服务器VNC无法连接及黑屏的原因)
- python3和python2 兼容(Python2和Python3的共存和切换使用)
- MongoDB 日志文件太大
- canvas 中增加组件(如何在Canvas中添加事件的方法示例)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9