赵工的个人空间

Python语言支持多种数据库操作，不过需要DB-API来访问。DB-API是一个规范，定义了一系列必需的对象和数据库存取方式，以便为各种底层数据库系统和多种数据库接口程序提供一致的访问接口。DB-API有关于模块属性的定义：
·apilevel：支持的DB-API版本
·threadsafety：线程安全级别
·paramstyle：支持的SQL参数风格
1）连接数据库connect：
Python可以使用的关系数据库有PostgreSQL、MySQL、SQLite、Oracle、SQL Server、Gadfly、Access等，为了使用数据库首先需要连接，使用connect函数。该函数有多个参数：

使用connect函数返回一个连接对象，该对象表示目前和数据库的会话。然后就可以使用该对象的方法对时刻中的数据进行操作了。
2）操作数据库：
操作数据库的常用方法有：

如果在程序中调用cursor()方法，会返回一个游标对象，然后可以通过游标对象执行SQL查询并查询结果。游标对象的特性为：

游标对象的常用方法有：

1. SQLite数据库的使用：

SQLite是一个开源的嵌入式数据库引擎，可用于多种操作系统。SQLite有许多第三方的数据库客户端工具用于对数据库对象进行操作，如SQLiteSpy、SQLiteManager。
Python自带了sqlite3模块，如果要使用SQLite数据库只需要导入这个模块。sqlite3模块的sqlite3.apilevel='2.0'，说明支持DB-API 2.0；sqlite3.threadsafety=1，表示初级线程支持，线程可共享模块，但不可共享连接；sqlite3.paramstyle='qmark'，表示问号风格，即“where name=?”的方式。
1）连接SQLite数据库：
格式为：
sqlite3.connect(database[, timeout, other optional arguments])
如果databse不存在，会自动创建。示例：
conn=sqlite3.connect('userDB.db')
返回的conn是一个数据库连接对象，然后就可以对数据库进行操作。一些常用操作：

2）SQL语句操作：
连接数据库成功后，需要创建游标，所有SQL操作都在游标对象中进行。创建游标：
cur=conn.cursor()
游标常用的属性有：

对游标的常用操作有：

3）SQLite操作步骤：
⑴导入sqlite3模块
⑵定义connect()创建数据库连接，返回对象conn
⑶调用conn.execute()方法创建表结构或插入数据，如果设置了手动提交则需要调用conn.commit()方法提交插入的数据
⑷调用conn.cursor()方法返回游标，然后通过cur.execute()方法查询数据库
⑸调用cur.fetchall()、cur.fetchmany()或cur.fetchone()方法返回查询结果
⑹关闭游标和数据库连接
示例：
import sqlite3
conn=sqlite3.connect('userDB.db')
conn.execute("create table if not exists address (id integer primary key autoincrement, name varchar(128), address varchar(128))")
conn.execute("insert into address (name, address) values ('tom', 'lundon')")
conn.execute("insert into address (name, address) values ('merry', 'new york')")
conn.commit()
cur=conn.cursor()
cur.execute("select * from address")
res=cur.fetchall()
print("address:",res)
for line in res:
    for f in line:
        print(f)
cur.close()
conn.close()
代码中，使用print("address:",res)语句输出的结果为：address: [(1, 'tom', 'lundon'), (2, 'merry', 'new york')]。

2. Access数据库：

要操作Access数据库，首先要安装pywin32。
1）建立数据库连接：
import win32com.client
conn=win32com.client.Dispatch(r'ADODB.Connection')
DSN='PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'
conn=Open(DSN)
2）打开记录集：
rs=win32com.client.Dispatch(r'ADODB.Recordset')
rs_name='MyRecordset'
rs.Open('['+rs_name+']', conn, 1, 3)
3）操作记录集：
rs.AddNew()
rs.Fields.Item(1).Value='data'
rs.Update()
4）操作数据：
conn=win32com.client.Dispatch(r'ADODB.Connection')
DSN='PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'
sql_statement="INSERT INTO [Table_Name] ([Field_1], [Field_1]) VALUES ('data1', 'data2')"
conn.Open(DSN)
conn.Execute(sql_statement)
conn.close()
5）遍历记录：
rs.MoveFirst()
count=0
while 1:
    if re.EOF:
        break
    else:
        count=count+1
    rs.MoveNext()
在操作Access数据库时，如果一个记录集是空的，那么将指针移到第一个记录集将导致一个错误，解决方法是打开记录集之前先将Cursorlocation设为3，然后打开记录集，此时RecordCount将是有效的。示例：
rs.Cursorlocation=3
rs.Open('SELECT * FROM [Table_Name]', conn)
re.RecordCount

3. MS SQL Server数据库：

可以使用pywin32、pymssql和pyodbc等多种方式来访问MS SQL Server数据库。
1）使用pywin32时可能需要使用adodbapi扩展库。示例：
import adodbapi
adodbapi.adodbapi.verbose=False
import adodapi.ado_consts as adc
⑴创建连接：
Cfg={'server':'192.168.29.86\\eclexpress','password':'xxxx','db':'pscitemp'}
constr=r"Provider=SQLOLEDB.1; Initial catalog=%s; Data Source=%s; user ID=%s;Password=%s;
  "%(Cfg['db'], Cfg['server'], 'sa', Cfg['password'])
conn=adodbapi.connect(constr)
⑵执行sql语句：
cur=conn.cursor()
sql='''SELECT * FROM softextBook WHERE title='{0}' AND remark3!='{1}''''.format(bookName, flag)
cur.execute(sql)
data=cur.fetchall()
cur.close()
⑶执行存储过程：
ret=cur.callproc('procName', (parm1, parm2, None))
conn.commit()
⑷关闭连接：
conn.close()
2）使用pymssql模块访问MS SQL Server数据库。
import pymssql
conn=pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase')
cur=conn.cursor()
cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))')
cr.executemany("INSERT INTO persons VALUES(%d, xinos.king)", [(1, 'John Doe'), (2, 'Jane Doe')])
conn.commit()
cur.excute('SELECT * FROM persons WHERE salesrep=xinos.king', 'John Doe')
row=cur.fetchone()
while row:
    print("ID=%d, Name=xinos.king"%(row[0], row[1]))
    row=cur.fetchone()
cur.execute('SELECT * FROM persons WHERE salesrep LIKE 'J%'")
conn.close()
如果提示无法导入pymssql模块，需要到网址www.lfd.uci.edu/~gohlke/pythonlibs下载whl文件，然后使用命令pip3安装。
3）使用pyodbc扩展库读取MS SQL Server数据库。
import pyodbc
s='DRIVER={SQL Server};SERVER=.;DATABASE=Test;UID=sa;PWD=test.'
conn=pyodbc.connect(s)
cur=conn.cursor()
cur.execute('SELECT * FROM yonghubiao')
row=cur.fetchone()
while row:
    print(row)
    row=cur.fetchone()
conn.close
如果提示无法导入pyodbc模块，需要到网址www.lfd.uci.edu/~gohlke/pythonlibs下载whl文件，然后使用命令pip3安装。

4. MySQL数据库：

MySQL数据库的Python接口有MySQL自带的MySQL Connector/Python与MySQLdb。MySQL Connector/Python针对不同的操作系统提供了相应的安装软件包。
1）连接数据库：
conn=MySQLdb.connect(host='hostname',db='databasename',user='user',passwd='password')
conn=mysql.connector.connect(user='root',password='123456',host='127.0.0.1')
2）使用MySQLdb模块，主要方法有：
·commit()：提交事务
·rollback()：回滚事务
·callproc(self, procname, args)：执行存储过程，参数为存储过程名和参数列表，返回行数
·execute(self, query, args)：执行单条SQL语句，参数为SQL语句和参数列表，返回行数
·executemany(self, query, args)：执行单条SQL语句，重复执行参数列表的参数，返回行数
·nextset(self)：移到下一个结果集
·fetchall(self)：接收全部的返回结果行
·fetchmany(self, size=None)：接收size条的返回结果行
·fetchone(self)：返回一条结果行
·scroll(self, value, mode='relative')：移动指针到某一行，如果mode='relative'表示从当前所在行移动value条记录，如果mode='absolute'表示从结果集的第一行移动value条记录
查询数据库示例：
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306)
    cur=conn.cursor()
    cur.execute('SELECT * FROM user')
    cur.close()
    conn.close()
except MySQLdb.Error as e:
    print("Mysql Error %d:%s" %(e.args[0], e.args[1]))
插入数据的用法示例：
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306)
    cur=conn.cursor()
    cur.execute('CREATE DATABASE IF NOT EXISTS python')
    conn.select_db('python')
    cur.execute('CREATE TABLE test (id int, info varchar(20))')
    value=[1, 'hi rollen']
    cur.execute('INSERT INTO test VALUES(%s, %s)', value')
    values=[]
    for i in range(20):
        values.append((i, 'hi rollen'+str(i)))
    cur.executemany('INSERT INTO test VALUES(%s, %s)', values)
    cur.execute('UPDATE test SET info="I am rollen" WHERE id=3')
    conn.commit()
    vur.close()
    conn.close()
except MySQLdb.Error as e:
    print("Mysql Error %d:%s" %(e.args[0], e.args[1]))

5. Psycopg2：

PostgreSQL是开源的关系数据库，能运行在多种操作系统上。PostgreSQL的Python接口主要有Psycopg 2、PyGreSQL、py-postgresql，Psycopg 2使用者比较多。
Python与PostgreSQL数据类型对应关系为：

1）连接数据库：
psycopg2.connect(dsn, connection_factory=None, cursor_factory=None, async=False)
psycopg2.connect(**kwargs, connection_factory=None, cursor_factory=None, async=False)
对应的示例：
conn=psycopg2.connect("dbname=testdb user=postgres password=secret")
conn=psycopg2.connect(database="testdb", user="postgres", password="123456", host="127.0.0.1", port="5432")
2）然后使用游标：
cur=conn.cursor()
3）使用SQL删除创建数据表：
cur.execute("DROP TABLE IF EXISTS todo")
cur.execute("'CREATE TABLE todo (id serial NOT NULL, title charracter varying(255) NOT NULL,
posted_on date, status boolean DEFAULT false, DateDue date, level integer,
CONSTRAINT todo_pkey PRIMARY KEY(id))"')
4）使用SQL操作数据库：
cur.execute("INSERT INTO todo (id, title, DateDue, level) VALUES(1, 'FIRST', '2015-12-12', 1)")
cur.execute("SELECT * FROM todo")
while True:
row=cur.fetchone()
if row==None:
    break
print(row[0],row[1],row[2])

6. ORM：

ORM是对数据库模式的描述，将Python对象映射到数据库行，并提供了一些用于从数据库中存取对象的接口。ORM将数据库表映射成一个Python类，表的字段就是这个类的成员变量，使所有的数据表都按照统一的标准映射，使系统在代码层面保持统一，可实现数据库转换，而无需SQL语句。Python ORM软件有SQLAlchemy、SQLObjec和Django的ORM。
1）创建引擎：
SQLAlchemy将数据库看作关系代数引擎，create_engine()返回引擎的实例，代表数据库的核心接口，建立与数据库连接，提供了Engine.execute()和Engine.connect()操作。
引擎使用URL格式：
dialect+drive://username:password@host:port/database
其中，dialect是指SQLAlchemy用于表示数据名，如sqlite、mysql、postgresql、oracle、mssql等；driver是DB-API名，如使用psycopg 2则为psycopy2；username、password、host、port、database分别表示数据库用户名、密码、主机地址、数据库端口、数据库名。示例：
engine=create_engine('postgresql://scott:tiger@localhost/mydatabase')
engine=create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
engine=create_engine('mysql://scott:tiger@localhost/mydatabase')
engine=create_engine('oracle://scott:tiger@127.0.0.1:1521/mydatabase')
engine=create_engine('oracle+cx_oracle://scott:tiger@tnsname')
engine=create_engine('sqlite:///mydatabase.db')
engine=create_engine('sqlite://') # 使用SQLite的内存数据库
2）数据操作：
SQLAlchemy主要使用mapper方法或扩展declarative。declarative语法允许执行创建表、类和数据库映射。
Base=declarative_base() # 创建一个类
class Todo(Base): # 继承Base类
  ......
3）创建数据表：
Base.metadata.create_all(engine)
4）会话Session：
Session为数据库句柄，用于建立所有与数据库的会话以及加载对象的存储，提供查询对象的入口点。查询对象使用会话对象的当前数据库连接发送数据库查询，并将查询接口填入Session中的对象。在Session内部，通常是添加数据到数据库、执行查询或删除数据。
会话的创建是通过sessionmaker的会话工厂函数创建，可在创建时绑定数据库引擎或在创建后通过configure()函数进行配置：
Session=sessionmaker(bind=engine)
使用add()添加实例到session。在执行commit()之前，数据库中并未实际产生数据，但通过query()可以查看数据。示例：
Session=sessionmaker(bind=engine)
session=Session()
todo1=Todo(id=1,title='test1',level=12,datedue=datetime.datetime.now())
try:
    session.add(todo1)    
    session.commit()
session还有add_all()、delete()、query()、flush()等方法。
5）query对象：
SQLAlchemy提供了query对象用于取回表中的数据，最基本操作是session.query(Todo).all()，返回表中的所有数据列表，其中的列可使用对象属性访问。query可以使用过滤功能：
alldata=session.query(Todo).filter(Todo.level>10).all()
filter在query对象上执行，实现过滤功能，还可以使用all()方法。query()对象还有filter_by()、limit()方法、offset()方法、group_by()方法等。可以通过以下方法显示相应的SQL语句：
str(session.query(Todo).filter(Todo.level>10))
Session还有rollback()方法，回滚当前事务。事务回滚后，所有事务撤销，添加到会话中挂起的对象被删除，标记删除的对象恢复连接状态。

7. MongoDB数据库：

MongoDB是基于分布式文件存储的文档数据库，是NoSQL中比较像关系型数据库的一种，对大数据支持较好。MongoDB数据结构有键值对组成，类似JSON对象，字段值可以包含其他文档、数组及文档数组。
1）下载安装连接：
到官网www.mongodb.org/downloads下载，安装后打开命令提示符环境，切换到MongoDB安装目录中的server\x.x\bin文件夹，在磁盘新建一个文件夹，如D盘下data，然后执行命令mongod --dbpath D:\data --journal --storageEngine=mmapv1启动MongoDB，如果连接成功就会显示一个“>”为提示符，然后就可以输入MongoDB命令了。
2）基本操作：
打开或创建数据库：
use students
在数据库中插入数据：
person1={'name':'tom','age':18,'sex':'male'}
db.students.insert(person1)
person2={'name':'lisi','age':19,'sex':'male'}
db.students.insert(person2)
查询数据库中的记录：
db.students.find()
查看系统中所有数据库名称：
show dbs
3）python扩展库操作：
pymongo支持MongoDB数据的操作，可以使用pip3安装。示例代码：
import pymongo
client=pymongo.MongoClient('localhost', 27017) #连接数据库，默认端口27017
db=client.students
db.collection_names() # 查看数据集合名称列表
students=db.students # 获取数据集合
students.find()
for item in students.find(): # 遍历数据
    print(item)
person3={'name':'peter','age':18,'sex':'male'}
students.insert(person3) # 插入一条记录
for item in students.find({'name':'peter'}): # 指定查询条件
    print(item)
students.find_one() # 获取一条记录
students.find_one({'name':'peter'}) # 指定查询条件获取一条记录
students.find().count() # 记录总数
students.remove({'name':'peter'}) # 删除一条记录
students.create_index([('name', pymongo.ASCENDING)]) # 创建索引
students.update({'name':'peter'}, {'$set':{'age':25}}) # 更新数据库
students.update({'age':25}, {'$set':{'sex':'female'}}) # 更新数据库
students.remove() # 清空数据库
tom={'name':'tom','age':18,'sex':'male'}
lisi={'name':'lisi','age':19,'sex':'male'}
peter={'name':'peter','age':18,'sex':'male'}
students.insert_many([tom, lisi, peter]) # 插入多条记录
for item in students.find().sort('name', pymongo.ASCENDING): # 对查询结果排序
    print(item)
for item in students.find().sort([('sex', pymongo.DESCENDING), ('name', pymongo.ASCENDING)]):    
    print(item)