python操作mysql, python操作postgres, mysql-pymysql, postgres-psycopg2 - pythonweb(2)

Python操作数据库 - python-web(2)

​      Java的开发者使用JDBC操作数据库， C开发者需要借助mysql-devel原生操作数据库，在python中如何操作数据库? 在python多用pymysql操作MySQL， psycopg2操作Postgresql。
​      python数据库操作模块都基本遵循 db-api，使得所有的模块能够统一化方法；python db-api主要关心两个：连接对象、游标对象，连接对象负责连接请求、数据打包、数据包解析，游标对象负责组织数据，两个对象之间是相互持有的。Connection对象有commit()、rollback()支持事务的方法，Cursor对象有execute(sql [, param])、fetchall()、fetchone()等操作SQL和结果集的方法，返回的结果通过元组组织。Cursor中还持有Connection对象作为成员，属性名依据具体实现而定。python操作数据库的一般流程：

• 获取连接， connect方法返回一个Connection对象conn

• 获取游标， conn.cursor()实例化一个游标对象cr

• SQL操作， cr.execute()执行SQL语句，DML(insert、update、delete)需要显示提交事务 conn.commit()

• 关闭游标， cr.close()

• 关闭连接， conn.close() 释放网络资源，关闭socket同时也会销毁socket所占有的缓冲区数据。

  关于数据库的搭建问题(Linux下)，已经在前面的文章说明了，本文只用到了表test，fruit- test(id, name)

(1) python操作mysql -pymysql:

    大型的关系型数据库都有自己的数据传输协议，mysql在应用层的协议是MYSQL。客户端pymql通过socket和服务器建立连接，构造数据包发送mysql-server， 解析myql-server返回的数据包。pymysql是纯python代码，通过网络编程实现的。

# 安装 pip installl PyMySQL

from pymysql import connect

# 数据库连接参数
db_config = {
    'host': '192.168.40.102',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'fruit',
}
conn = connect(**db_config)
cr = conn.cursor()
cr.execute("SELECT * FROM test")
res = cr.fetchall()
cr.close()
conn.close()

(2) python操作postgresql - psycopg2:

    postgres在应用层的数据协议是POSGRESQL， psycopg2是cpython写的，完全按照原生C连接数据库一样操作，将数据结构重新组织，所以psycopg2我们看不到python源码，因为在import时已经将静态库加载进来了。

 #安装： pip install psycopg2
 
 from psycopg2 import connect
 
 # 数据库连接参数
 db_config = {
     'host': '192.168.40.102',
     'port': 5432,
     'user': 'postgres',
     'password': '123456',
     'database': 'fruit',
 }
 
 conn = connect(**db_config)
 cr = conn.cursor()
 cr.execute("SELECT * FROM test")
 res = cr.fetchall()
 cr.close()
 conn.close()

执行动态SQL语句：

# 直接执行SQL语句
sql_1 = "SELECT * FROM test WHERE id=1 And name='test'"
cr.execute(sql_1)

# 用变量拼接SQL语句
data = {'id':1, 'name':'test'}
sql_2 = "SELECT * FROM test WHERE id = {id} AND name = '{name}'".format(**data)
cr.execute(sql_2)

# 带参数执行
sql_3 = "SELECT * FROM test WHERE id = %s AND name = '%s'"
param_3 = list(data.values())
cr.execute(sql_3, param_3)

(3) pymysql返回字典、psycopg2返回字典：

    cr.fetchone() 默认返回的是一条元组，如何让返回的数据以字典的形式组织呢？在模块本身就给出了方案。以字典的方式返回数据更符合web框架下的需求。

# pymysql
from pymysql.cursors import DictCursor
cr = conn.cursor(DictCursor)

# psycopg2
from psycopg2.extras import RealDictCursor
cr = conn.cursor(cursor_factory=RealDictCursor)

这个问题的实质还是数据库数据协议，执行SELECT语句返回的数据包里，首先是描述SELECT列字段信息的数据，组织在cursor的description字段里，在cr.execute()执行完后，数据已经返回到了客户端。

fields = [d[0] for d in cr.description]

(4) python数据库连接池 - DBUtils

​     数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数制约。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数。

​      连接池基本的思想是在系统初始化的时候，将数据库连接作为对象存储在内存中，当用户需要访问数据库时，并非建立一个新的连接，而是从连接池中取出一个已建立的空闲连接对象。使用完毕后，用户也并非将连接关闭，而是将连接放回连接池中，以供下一个请求访问使用。python-DBUtils的使用：

from dbutils.pooled_db import PooledDB
import pymysql

conn_config = {
    'creator': pymysql,	
    'maxconnections': 120,		
    'mincached': 100,			
    'maxcached': 120,
}

db_config = {
    'host': '192.168.40.102',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'fruit',
}

pool = PooledDB(
    **{**conn_config, **db_config}
)

conn = pool.connection()        # borrow an connenction from pool
cr = conn.cursor()
cr.execute("SELECT * FROM test")
res = cr.fetchone()
cr.close()  # or del cur
conn.close()                    # give back the connection to pool

# conn.close() 是向连接池归还当前连接对象，同时会清空连接对象里当前执行SQL语句所缓存的数据
'''
连接池参数说明
mincached： 初始化时创建连接的数量
maxcached： 连接池所能创建的最大连接数量
maxconnections： 连接池允许的最大连接数两
maxusage： 单一连接的最大复用次数
blocking: 当连接池到达最大连接数量时是否等待
'''