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  阅读(1887)  评论(0)  编辑  收藏  举报