使用Python操作MySQL和Oracle数据库

前  言

前面两篇文章已经说过将数据存储到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安装即可

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，，命令入下：

pip3 install pymysql

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

连接数据库

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

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

from sqlalchemy import create_engine as ce
engine = ce("mysql+pymysql://root:password@localhost:3306/test?charset=utf-8",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), ForeignKey(mytable.name)),
                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语句，所用代码如下。

from sqlalchemy.orm import sessionmaker

DBSession = sessionmaker(bind=engine)

session = DBSession()

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

new_data = mytable(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('My name is :' + i.name)
    print('My class 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则可省略不写。i

mport 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框架，加油！若想获取更多，可关注公众号【JiekeXu之路】，ID为【JiekeXu_IT】

参考资料

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

---