Python3 连接各类数据库

Python 数据库接口支持非常多的数据库：MySQL 、 PostgreSQL、Microsoft SQL Server 2000、 Informix 、Interbase、Oracle 、Sybase。通过python连接MySQL可以通过以下python库：

MySQLdb
MySQLdb 是用于Python链接Mysql数据库的接口，MySQLdb又叫MySQL-python ，是 Python 连接 MySQL 最流行的一个驱动，很多框架也是依据其开发的。只支持python2.x。

Mysqlclient
完全兼容MySQLdb的衍生版；同时兼容Python3.x，原生SQL操作数据库。由于MySQLdb年久失修，后来出现了它的 Fork 版本 mysqlclient，完全兼容 MySQLdb

PyMysql
纯Python实现的驱动。速度不如MySQLdb，兼容MySQLdb，推荐使用PyMysql连接mysql

Python里封装的数据库操作很简单：
1、获取连接conn；
2、获取游标cursor；
3、使用cursor.execute()执行SQL语句；
4、使用cursor.rowcount返回执行insert，update，delete语句受影响的行数；
5、使用cursor.fetchall()获取查询的结果集。结果集是一个list，每个元素都是一个tuple，对应一行记录；
6、关闭游标和连接。

如果SQL语句带有参数，那么需要把参数按照位置传递给cursor.execute()方法，有几个?占位符就必须对应几个参数，示例：

cursor.execute('select * from user where name=? ', ['abc'])
为了能在出错的情况下也关闭掉Connection对象和Cursor对象，建议实际项目里使用try:...except:...finally:...结构。

一、SQLite

SQLite是一种嵌入式数据库，SQLite本身是C写的体积很小，它的数据库就是一个文件，所以经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。Python内置了sqlite3。在Python中使用SQLite，不需要安装任何东西，直接使用。

在使用SQLite前先要搞清楚几个概念：表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，表和表之间通过外键关联。要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；连接到数据库后，需要打开游标（Cursor），通过Cursor执行SQL语句，然后获得执行结果。

import sqlite3

conn = sqlite3.connect('D:\\test.db') # 连接到SQLite数据库，数据库文件是test.db 如果文件不存在，会自动在当前目录创建
cursor = conn.cursor() # 创建一个Cursor

# sqlite创建表时，若id为INTEGER类型且为主键，可以自动递增，在插入数据时id填NULL即可
# cursor.execute('create table user(id integer primary key, name varchar(25))') 

# 插入一条数据
cursor.execute('insert into user(id,name)values(NULL,"yjc")')
conn.commit() #提交事务，否则上述SQL不会提交执行

# 执行查询
cursor.execute('select * from user')
print(cursor.fetchall()) # 获取查询结果  [(1, 'yjc')]

# 关闭游标和连接
cursor.close()
conn.close()

二、Mysql

1. 安装 PyMysql 库
pip3 install pymysql

2. 连接数据库的几种方法

第一种：将各类字段写上
db = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", db="Geek_Web", charset="utf8mb4")

第二种：省略字段
db = pymysql.connect(root,root, Geek_Web)

第三种：构建配置文件
config = {
'host':'localhost',
'port':3306,
'user':'root',
'passwd':'root',
'db':'Geek_Web',
'charset':'utf8mb4',
}
db = pymysql.connect(**config)

connect对象：

db = pymysql.connect(参数列表)

1、host ：主机地址,本地 localhost

2、port ：端口号,默认3306

3、user ：用户名

4、password ：密码

5、database ：库

6、charset ：编码方式,推荐使用 utf8

连接对象的方法：

数据库连接对象(db)的方法

1、db.close() 关闭连接

2、db.commit() 提交到数据库执行

3、db.rollback() 回滚

4、cur = db.cursor() 返回游标对象,用于执行具体SQL命令

游标对象的方法：

游标对象(cur)的方法

1、cur.execute(sql命令,[列表]) 执行SQL命令

2、cur.close() 关闭游标对象

3、cur.fetchone() 获取查询结果集的第一条数据

4、cur.fetchmany(n) 获取n条

5、cur.fetchall() 获取所有记录

PyMysql 默认返回是元组，在连接数据库时候加上 cursorclass=pymysql.cursors.DictCursor 就可以将数据库内容以字典格式返回

import pymysql

# 连接数据库0
def Mysql_test0():
    config = {
    'host':'localhost',
    'port':3306,
    'user':'root',
    'passwd':'lwwroot',
    'db':'sakila',
    'charset':'utf8mb4',
    'cursorclass':pymysql.cursors.DictCursor #将数据库内容以字典格式返回，非必须参数
    }
    db = pymysql.connect(**config)
    cursor = db.cursor() #获取操作游标 
    try:
        return (db, cursor)
    except:
        print("数据库访问失败")

# 连接数据库1
def Mysql_test():
    # 连接数据库，cursorclass=pymysql.cursors.DictCursor可以使数据库内容以字典格式返回，非必须参数
    db = pymysql.connect(host="localhost", port=3306, user="root", passwd="lwwroot", db="test",cursorclass=pymysql.cursors.DictCursor, charset="utf8")
    #方法获取操作游标
    cursor = db.cursor()
    try:
        return (db, cursor)
    except:
        print("数据库访问失败")

db, cursor = Mysql_test()

cursor.execute("SELECT * FROM city")  #查询数据
print(cursor.fetchall())

# cursor.execute("insert into city(ID, Name, CountryCode, Population) values (1002, 'shanghai', 'CN', 4000000)") #插入数据
# db.commit()

# cursor.execute("update city set ID=1003 where name='beijing'") #修改数据
# db.commit()

# cursor.execute("delete from city where name='shanghai'")  #删除数据
# db.commit()

cursor.close()
db.close()

三、Mssql

1. 安装 PyMssql 库
pip3 install pymssql

2. 连接数据库的方法
Mssql 用字典配置不可以用，connect() 用来连接数据库
db = pymssql.connect(host="192.0.0.200",user="ymyg",password="ymyg",database="Geek_Web")

3. 操作数据库
和 Mysql 操作方法一模一样，只不过将 PyMysql 改为 PyMssql 即可，参考上面 PyMssql

4. PyMssql 返回字典数据
只需要在连接数据库时候加上一个 as_dict 字段，将值改为 True 即可
db = pymssql.connect(host="192.0.0.200",user="ymyg",password="ymyg",database="Geek_Web",as_dict=True)

5. PyMssql 参数
connect() 参数

dsn 连接字符串主要用于与之前版本的pymssql兼容
user 用户名
password 密码
trusted 布尔值指定是否使用windows身份认证登陆
host 主机名
database 数据库
timeout 查询超时
login_timeout 登陆超时
charset 数据库的字符集
as_dict 布尔值指定返回值是字典还是元组
max_conn 最大连接数

操作方法

close() 关闭游标
execute(operation) 执行操作
execute(operation params) 执行操作可以提供参数进行相应操作
executemany(operation paramsseq) 执行操作 Paramsseq 为元组
fetchone() 在结果中读取下一行
fetchmany(size=None) 在结果中读取指定数目的行
fetchall() 读取所有行
nextset() 游标跳转到下一个数据集

其他方法

autocommit(status) 布尔值指示是否自动提交事务默认的状态是关闭的如果打开你必须调用commit()方法来提交事务
close() 关闭连接
cursor() 返回游标对象用于查询和返回数据
commit() 提交事务
rollback() 回滚事务
pymssqlCursor类用于从数据库查询和返回数据
rowcount 返回最后操作影响的行数
connection 返回创建游标的连接对象
lastrowid 返回插入的最后一行
rownumber 返回当前数据集中的游标（通过索引）

import time
import pymssql

class MSSQL:
  def __init__(self,host,user,pwd,db):
    self.host=host
    self.user=user
    self.pwd=pwd
    self.db=db

  def GetConnect(self):
    if not self.db:
      raise(NameError,'没有目标数据库')
    self.connect=pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset='utf8')
    cur=self.connect.cursor()
    if not cur:
      raise(NameError,'数据库访问失败')
    else:
      return cur

  def ExecSql(self,sql):
     cur=self.GetConnect()
     cur.execute(sql)
     self.connect.commit()
     self.connect.close()

  def ExecQuery(self,sql):
    cur=self.GetConnect()
    cur.execute(sql)
    resList = cur.fetchall()
    self.connect.close()
    return resList  

def main():
  ms = MSSQL(host="192.168.0.108", user="sa", pwd="sa", db="ComPrject")
  resList = ms.ExecQuery("select *from TestModel")
  print(resList)

if __name__ == '__main__':
  main()
  print("执行完成")

四、Oracle

1. 安装 cx_Oracle 库
pip3 install cx_Oracle

2.Oracle instant client 下载安装
下载地址http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html。然后解压下载的压缩包，并将对应的解压位置加入系统变量Path中，将Oracle instant client目录下的oraocci12.dll、oraociei12.dll、oci.dll复制到python安装目

3. 连接数据库的几种方法
第一种：Oracle 连接方法
db = cx_Oracle.connect('root/root@localhost: 1523/orcl')

第二种：省略字段连接方法
db = cx_Oracle.connect('root', 'root', 'localhost: 1523/orcl')

第三种：dsn 方法
makedsn(IP/HOST, PORT, TNSNAME)
dsn = cx_Oracle.makedsn('localhost','1523','orcl')
db = cx_Oracle.connect('root','root',dsn)

4. 操作数据库
和 Mysql 操作方法一模一样，只不过将 PyMysql 改为 cx_Oracle 即可

5. PyMssql 返回字典数据

Oracle 返回字典类型比较麻烦，因为 cx_Oracle 没有集成，所以需要我们自己写返回字典的方法。cx_Oracle 的 Cursor 对象有一个属性 rowfactory 是是用来自定义查询结果的预处理方法的，定义一个闭包

def makedict(cursor):

cols = [d[0] for d in cursor.description]

def createrow(*args):

return dict(zip(cols, args))

return createrow

并将其注册给游标对象的rowfactory属性 cursor.rowfactory = makedict(cursor) 得到的结果自动由元组转为字典了，但要注意，注册的动作需要在每次执行 cursor.execute 之后都重复一次。最终的方法是定义了一个类来继承 Cursor 对象，这样就不需要重复注册了

import cx_Oracle

#连接数据库，下面括号里内容根据自己实际情况填写
conn = cx_Oracle.connect('用户名/密码@IP:端口号/SERVICE_NAME')

# 使用cursor()方法获取操作游标
cursor = conn.cursor()

#使用execute方法执行SQL语句
result=cursor.execute('Select member_id from member')


#获取所有数据
all_data=cursor.fetchall()
#使用fetchone()方法获取一条数据
#data=cursor.fetchone()
#获取部分数据，8条
#many_data=cursor.fetchmany(8)

print (all_data)

db.close()

posted @ 2018-01-05 01:52 liangww 阅读(1809) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部