前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >使用Python操作MySQL和Oracle数据库

使用Python操作MySQL和Oracle数据库

作者头像
JiekeXu之路
发布2019-08-15 15:01:01
2.8K0
发布2019-08-15 15:01:01
举报
文章被收录于专栏:JiekeXu之路

点击蓝字

关注我们

前 言

前面两篇文章已经说过将数据存储到SQLite和本地文件中,如果还没有来得及看,可点击如下快速链接:

https://mp.weixin.qq.com/s/C3pJpCyjvw7ZXzUCQB37zw

今天继续分享一下将数据存储到关系型数据库MySQL和Oracle。那么对于DBA来说数据库是一个说不完的话题,这里也不打算展开说明,请自行在Windows下安装MySQL和Oracle即可。Python操作数据库一般使用两种方法,前面使用SQLite时便调用了数据库连接接口,这里也可以使用数据库接口pymysql和cx_Oracle来对数据库操作,但是学习是不断积累的过程,前面已经介绍过类似的方案,这里则使用另一种方案:通过ORM(object/Relation Mapping,对象-关系映射)框架来连接数据库。

在实际的工作中,企业级开发都是使用ORM框架来实现数据库持久化操作的,所以学习ORM框架还是很有必要的,而常见的ORM框架模块有SQLObject、Stom、Django的ORM、peewee和SQLalchemy。本次主要学习的是Python的ORM框架——SQLAlchemy,此框架于2006年2月首次发行,是Python社区中被广泛使用的ORM工具之一,不亚于Django的ORM框架,它还支持众多数据库,如SQLite、MySQL、Postgre、Oracle、MS-SQL、SQLServer和Firebird,它的目标是作为企业级持久性模型提供开源的服务。

安装SQLAlchemy

在Python环境下直接使用pip安装即可。

代码语言:javascript
复制
pip install SQLAlchemy  

除了通过pip安装外还可以在www.lfd.uci.edu/~gohlke/pythonlibs/#sqlalchemy下载你系统对应位数的SQLAlchemy的whl版本。whl版本也可以使用pip安装,在CMD窗口下,切换到文件所在目录,执行安装命令:

pip3 install SQLAlchemy‑1.2.15‑cp37‑cp37m‑win_amd64.whl

安装完之后还需要安装MySQL数据库接口模块pymysql,,命令入下:

代码语言:javascript
复制
pip3 install pymysql

备注:一直使用pip3是因为系统中已经安装了Python2,故pip也是使用pip3

连接MySQL数据库

Python连接数据库之前,得先准备好MySQL数据库,由于篇幅问题这里不再说明软件下载和安装过程,请自行Google,只简单介绍环境配置,MySQL采用5.7.17GPL版本,数据库是本地数据库,端口为默认的3306,使用数据库名为test,如下图所示。

数据库准备妥当后,我们来一起看看Python是怎么连接的呢?这里给出连接代码:

from sqlalchemyimport create_engine as ce engine = ce("mysql+pymysql://root:root@localhost:3306/test?charset=utf8",echo=True,pool_size=5,max_overflow=4,pool_recycle=7200,pool_timeout=30)

这里简单说明一下连接参数的含义吧,数据库类型+数据库接口模块://数据库用户名:密码@IP:端口号/数据库名;charset=utf-8是对数据库编码进行设置,以防读取中文时编码错误(这里先说明一下,要是字符类型写成utf-8创建表时铁定报错,需要写成charset=utf8);echo=True用于显示SQLalchemy在操作数据库时所执行的SQL语句情况,相当于一个监视器,可以清楚的知道执行情况;pool_size=5为设置连接数,默认就是5,可根据实际情况调整,但一般开发中5个连接够用;max_overflow=4默认连接数为10,当超出最大连接数后,如果超出的连接数在max_overflow设置的访问内,超出的部分还可以继续连接访问,在使用过后,在使用过后,这部分连接不放在pool(连接池)中,而是被真正关闭;pool_recycle为连接重置周期,默认为-1,推荐设置为7200,即如果连接已空闲7200秒,就自动重新获取,以防止connection被关闭;pool_timeout为连接超时时间,默认为30秒,超过时间的连接都会连接失败。

创建数据库表

from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,DateTime Base = declarative_base() class mytable(Base): __table__='mytable' #以下为字段和属性 id = Column(Integer,primary_key=True) name = Column(String(50),unique=True) age = Column(Integer) birth = Column(DateTime) class_name = Column(String(50)) #创建数据表 Base.metadata.create_all(engine)

当执行创建数据表的命令时报错了,“ AttributeError: 'NoneType' object has no attribute 'encoding' ”看到这个应该是字符串的问题,一边去找报错的connections.py文件,一边去找度娘,发现是说提供的编码类型错误为【utf-8】而实际上编码应该是【utf8】,知道踩坑了,于是乎去检查,原来是数据库连接串中写为“charset=utf-8”,于是乎改掉连接串重新操作。

执行以上创建表语句则已经创建好了表”mytable”,打开MySQL,切换到test数据库,查询表如下图所示。

以上创建表由程序创建,也是比较常见的方式之一,下面有一种类似SQL语句的创建方法。

from sqlalchemy import Column, MetaData, ForeignKey, Table from sqlalchemy.dialects.mysql import (INTEGER, CHAR) meta = MetaData() myclass = Table('myclass',meta, Column('id', INTEGER,primary_key=True), Column('name', CHAR(50)), Column('class_name',CHAR(50)) ) myclass.create(bind=engine) #创建数据表 #删除数据表,删除表时先删除myclass然后删除mytable,因为myclass表中存在外键,必须先删除有外键的表才可以 myclass.drop(bind=engine)

但是当第二次使用上面语法创建时已经关闭了以上窗口,重新连接数据库,创建myclass表时会报错:“NameError: name 'mytable' is not defined”,无奈,只能重新执行上面步骤,却报错了“ AttributeError: 'str' object has no attribute 'c' ”,怀疑是自己环境问题,随之又使用了Anaconda安装了SQLAlchemy1.2.7,MySQL采用5.7.17,但是还是报错一样,这里贴图如下,希望有路过的大神斧正,不胜感激。网上找了好久,百思不得其解,于是乎又去换个环境,采用Ubuntu16.04,Python3.6.5安装MySQL5.7.13,SQLAlchemy1.2.15版本还是相同的报错,绝望中!

>>> from sqlalchemy import create_engine >>> engine = create_engine("mysql+pymysql://root:root@localhost:3306/test?charset=utf8",echo=True,pool_size=5,max_overflow=4,pool_recycle=7200,pool_timeout=30) >>> >>> from sqlalchemy import Column, Integer, String, DateTime >>> from sqlalchemy.ext.declarative import declarative_base >>> Base = declarative_base() >>> >>> class mytable1(Base): ... __table__= 'mytable1' ... ... id = Column(Integer, primary_key=True) ... name = Column(String(50), unique=True) ... age = Column(Integer) ... birth = Column(DateTime) ... class_name = Column(String(50)) ... Traceback (most recent call last): File "<stdin>", line 1, in <module> File "C:\Users\Administrator\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\ext\declarative\api.py", line 65, in __init__ _as_declarative(cls, classname, cls.__dict__) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\ext\declarative\base.py", line 116, in _as_declarative _MapperConfig.setup_mapping(cls, classname, dict_) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\ext\declarative\base.py", line 144, in setup_mapping cfg_cls(cls_, classname, dict_) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\ext\declarative\base.py", line 172, in __init__ self._setup_table() File "C:\Users\Administrator\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\ext\declarative\base.py", line 495, in _setup_table if not table.c.contains_column(c): AttributeError: 'str' object has no attribute 'c' >>>

后面猜测是SQLAlchemy包的问题,对此包掌握的不是很熟悉,故采用第二种建表方法,如下所示。

from sqlalchemy import Column, MetaData, DateTime, Table from sqlalchemy.dialects.mysql import (INTEGER, CHAR) meta = MetaData() mytable = Table('mytable',meta, Column('id', INTEGER, primary_key=True), Column('name', CHAR(50)), Column('age', INTEGER), Column('birth',DateTime), Column('class_name', CHAR(50)) ) mytable.create(bind=engine) from sqlalchemy import Column, MetaData, ForeignKey, Table from sqlalchemy.dialects.mysql import (INTEGER, CHAR) meta = MetaData() myclass = Table('myclass',meta, Column('id', INTEGER, primary_key=True), Column('name', CHAR(50)), Column('class_name', CHAR(50)) ) myclass.create(bind=engine) #CREATE TABLE myclass (id INTEGER NOT NULL AUTO_INCREMENT,name CHAR(50),class_name CHAR(50),PRIMARY KEY (id)

插入数据

当建立好表关系后,需要将数据插入到表中,对其进行相关的操作前,需要创建一个会话对象用于执行SQL语句,所用代码如下。

fromsqlalchemy.orm import sessionmaker

DBSession =sessionmaker(bind=engine)

session =DBSession()

引入sessionmaker模块,指定绑定已连接数据库的engine对象,生成会话对象session,该对象用于数据库的增、删、改、查。那么创建表添加数据的语法如下。

new_data = mytab(name= ‘zhangdan’,age=7,birth=’2010-12-22’,class_name=‘class1’) session.add(new_data) session.commit session.close()

update更新数据

session.query(mytable).filter_by(id= 1 ).update ({mytable.age:12}) session.commit session.close()

查询数据

查询数据时,由于上表数据较少,先插入一些数据,方便内容展示

insert into myclass values(2,'xiaoxiao','class1'); insert into myclass values(3,'xiaoli','class2'); insert into myclass values(4,'xiaoma','class2'); insert into myclass values(5,'xiaowang','class1');

查询数据时,可查看全部数据,特定字段的数据以及条件查询数据

#查询全部数据 get_data = session.query(myclass).all() print(get_data) #查询特定字段数据 get_data =session.query(myclass.name,myclass.class_name).all() print(get_data) #按条件查询 get_data =session.query(myclass).filter_by(class_name='class1').all() print('数据类型是:' + str(type(get_data))) for i in get_data: print('Myname is :' + i.name) print('Myclass is :' + i.class_name)

好啦,关于SQLAlchemy和MySQL就说这么多了,使用SQLAlchemy过程中可算是遇到了数不清的坑,而且花费两周末都是找不到原因,可怕的是第一次可以,第二次就报错,着实是让我无解,结尾也会放置采坑过程中的链接,供大家参考。对SQLAlchemy框架不熟悉的建议还是使用标准的API接口来连接数据库比较好,那么下面就继续说说使用python来操作Oracle数据库。

连接Oracle数据库

使用Python连接Oracle时,和MySQL不同的是必须要启用监听,这里使用的是Windows版本的Oracle11g,具体的安装过程这里不再演示,如若需要Windows下Oracle11g安装包和安装教程,后台私信我即可。这里直接从连接开始,首先使用win+R打开CMD,输入services.msc回车打开服务,找到Oracle监听程序,点击启动此服务即可。

通过CMD查看监听状态也是已经启动,使用sqlplus连接到数据库,也可使用工具PLSQL远程连接,需要注意监听状态是否正常以及监听端口是否为默认的1521。

下面通过Python连接,需要借助第三方的cx_Oracle包,使用pip3 install cx_Oracle即可。

pip3 install cx_Oracle --查看安装版本 >>> import cx_Oracle >>> cx_Oracle.__version__ '7.0.0'

创建数据库连接connect和关闭数据库连接close

创建数据库连接的三种方式:

方法一:用户名、密码和监听分开写 import cx_Oracle db=cx_Oracle.connect('username/password@host:port/orcl') db.close() 方法二:用户名、密码和监听写在一起 import cx_Oracle db=cx_Oracle.connect('username','password','host:1521/orcl') db.close() 方法三:配置监听并连接 import cx_Oracle tns=cx_Oracle.makedsn('host',1521,'orcl') db=cx_Oracle.connect('username','password',tns) db.close()

查询数据

这里使用第一种方法配置即可,不再使用前面的SQLAlchemy方法,若端口为默认的1521则可省略不写。

import cx_Oracle #连接数据库 db = cx_Oracle.connect('scott/scott@localhost:1521/orcl') #打开游标 cur = db.cursor() #执行SQL sql = " select sysdate from dual" cur.execute(sql) data = cur.fetchone() print('Database time:%s' % data) #提交、关闭游标 cur.close() db.close()

创建数据库表并插入数据

db = cx_Oracle.connect('scott/scott@localhost:1521/orcl') cur = db.cursor() cur.execute ("CREATE TABLE my_job(id INT, name VARCHAR(40), age INT, job VARCHAR(50))") cur.execute ("INSERT INTO my_job (id, name, age)VALUES(12,'xiaoliu', 32)") cur.execute ("INSERT INTO my_job (id, name, age)VALUES(13,'xiaoli', 23)") cur.execute ("INSERT INTO my_job (id, name, age,job)VALUES(14,'xiaoma',36,'IT')") cur.execute ("INSERT INTO my_job VALUES(15,'xiaocai',36,'worker')") db.commit() #这里一定要commit才行,要不然数据是不会插入的 cur.execute("SELECT * FROM my_job") # 提取一条数据,返回一个元祖 data = cur.fetchone() print(data) cur.close() db.close()

删除数据并查询

删除数据也很简单,连接数据库打开游标,执行SQL提交,关闭游标,关闭数据库,具体代码如下。

#删除数据 db = cx_Oracle.connect('scott/scott@localhost:1521/orcl') cur = db.cursor() cur.execute ("delete from my_job where id=12") db.commit() cur.execute("SELECT * FROM my_job") rows = cur.fetchall() for row in rows: print("%d, %s, %d, %s" % (row[0], row[1], row[2], row[3])) cur.close() db.close()

写在最后

Python使用标准的API接口操作数据库是很简单的,主要有六大步:连接数据库、打开游标、执行SQL、提交、关闭游标、关闭数据库。MySQL使用pymysql包,Oracle使用cx_Oracle包都能够很好的操作数据库,但要是使用了SQLAlchemy的ORM框架来操作数据库,感觉会遇到无数坑等你跳,还是得认真学习ORM框架,加油!最后,文中所有代码不可滑动,看起来不太方便,如有需要文中代码,后台回复【1224】即可获取!

参考资料

utf8错误参考:https://blog.csdn.net/zlsdmx/article/details/84836240

SQLAlchemy框架错误:https://stackoverflow.com/questions/48473140/sqlalchemy-exc-noreferencedtableerror-foreign-key-associated-with-column-x-coul

https://stackoverflow.com/questions/45845007/python-sqlalchemy-getting-table-object-is-not-callable-error

ORM框架:https://www.cnblogs.com/pycode/p/mysql-orm.html

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2018-12-24,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 JiekeXu之路 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 插入数据
    • 创建数据库连接connect和关闭数据库连接close
      • 查询数据
        • 创建数据库表并插入数据
          • 删除数据并查询
          相关产品与服务
          云数据库 SQL Server
          腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
          领券
          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档