前  言

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

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

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

安装SQLAlchemy

pip install SQLAlchemy  

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

pip3 install pymysql

连接MySQL数据库

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)

创建数据库表

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)

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)

>>> 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'

>>>

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)

插入数据

fromsqlalchemy.orm import sessionmaker

DBSession =sessionmaker(bind=engine)

session =DBSession()

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)

连接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()

查询数据

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()

删除数据并查询

#删除数据

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()

写在最后

参考资料

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

Windows环境下基于Anaconda的Python3安装

使用Python爬取《悲伤逆流成河》猫眼信息

(端午节福利)各大影视VIP解析视频观看方法

让我们来聊一聊微信（基于Python分析）

推荐一个好用的PDF阅读器—悦书阅读器

基于Python的SQLite基础知识学习

使用Python将图片变成铅笔素描