python操作数据库

一、pymysql

1、pymysql介绍

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库。

2、操作示例

查询数据库内容，并保存为excel文件。

def DataGet():
    # Use a breakpoint in the code line below to debug your script.
    # 打开数据库连接
    now_time = time.strftime("%Y_%m_%d_%H")
    persons_excel = xlsxwriter.Workbook("./hw_performance_" + now_time + ".xlsx")

    sheet = persons_excel.add_worksheet("sheet")

    db = pymysql.connect(host="127.0.0.1", port=3306, user="mini", password="mini123", database="mytest",charset="utf8")

    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()

    # 使用 execute()  方法执行 SQL 查询
    cursor.execute("SELECT * FROM userinfo")

    # 使用 fetchall() 方法获取s所有数据.
    data = cursor.fetchall()
    row = len(data)  # 获取表的行数
    print(row)
    print("\n\n****************\n\n")
    print(data)
    print("****************\n\n")
    col = cursor.description
    print(col)
    table_header = []
    for i in range(len(col)):
        table_header.append(col[i][0])
        sheet.write_row(0, 0, table_header)  # 把表头写进去

    print(table_header)

    k = 0

    for i in data:
        k = k + 1
        sheet.write_row(k, 0, i)

    persons_excel.close()

    # 关闭数据库连接
    db.close()

二、dbutils

1、dbutils介绍

DBUtils 是一套用于管理数据库连接池的包，为高频度高并发的数据库访问提供更好的性能，可以自动管理连接对象的创建和释放。最常用的两个外部接口是 PersistentDB 和 PooledDB，前者提供了单个线程专用的数据库连接池，后者则是进程内所有线程共享的数据库连接池。

python编程中可以使用MySQLdb进行数据库的连接及诸如查询/插入/更新等操作，但是每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。
安装数据库连接池模块DBUtils

pip3 install DBUtils

DBUtils提供两种外部接口：

• PersistentDB ：提供线程专用的数据库连接，并自动管理连接。
• PooledDB ：提供线程间可共享的数据库连接，并自动管理连接。

    dbapi ：数据库接口
    mincached ：启动时开启的空连接数量
    maxcached ：连接池最大可用连接数量
    maxshared ：连接池最大可共享连接数量
    maxconnections ：最大允许连接数量
    blocking ：达到最大数量时是否阻塞
    maxusage ：单个连接最大复用次数

根据自己的需要合理配置上述的资源参数，以满足自己的实际需要。

2、操作示例

需要注意的是import DBUtils，必须写小写

**from dbutils.pooled_db import PooledDB**

class MyPymysqlPool(BasePymysqlPool):
    """
    MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现
        获取连接对象：conn = Mysql.getConn()
        释放连接对象;conn.close()或del conn
    """
    # 连接池对象
    __pool = None

    def __init__(self, conf_name=None):
        self.conf = Config().get_content(conf_name)
        super(MyPymysqlPool, self).__init__(**self.conf)
        # 数据库构造函数，从连接池中取出连接，并生成操作游标
        self._conn = self.__getConn()
        self._cursor = self._conn.cursor()

    def __getConn(self):
        """
        @summary: 静态方法，从连接池中取出连接
        @return MySQLdb.connection
        """
        if MyPymysqlPool.__pool is None:
            __pool = PooledDB(creator=pymysql,
                              mincached=1,
                              maxcached=20,
                              host=self.db_host,
                              port=self.db_port,
                              user=self.user,
                              passwd=self.password,
                              db=self.db,
                              use_unicode=True,
                              charset="utf8",
                              cursorclass=DictCursor)
            print("12211212")
        return __pool.connection()

    def getAll(self, sql, param=None):
        """
        @summary: 执行查询，并取出所有结果集
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list(字典对象)/boolean 查询到的结果集
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchall()
        else:
            result = False
        return result

    def getOne(self, sql, param=None):
        """
        @summary: 执行查询，并取出第一条
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list/boolean 查询到的结果集
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchone()
        else:
            result = False
        return result

    def getMany(self, sql, num, param=None):
        """
        @summary: 执行查询，并取出num条结果
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param num:取得的结果条数
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list/boolean 查询到的结果集
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchmany(num)
        else:
            result = False
        return result

    def insertMany(self, sql, values):
        """
        @summary: 向数据表插入多条记录
        @param sql:要插入的ＳＱＬ格式
        @param values:要插入的记录数据tuple(tuple)/list[list]
        @return: count 受影响的行数
        """
        count = self._cursor.executemany(sql, values)
        return count

    def __query(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        return count

    def update(self, sql, param=None):
        """
        @summary: 更新数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def insert(self, sql, param=None):
        """
        @summary: 更新数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def delete(self, sql, param=None):
        """
        @summary: 删除数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要删除的条件 值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def begin(self):
        """
        @summary: 开启事务
        """
        self._conn.autocommit(0)

    def end(self, option='commit'):
        """
        @summary: 结束事务
        """
        if option == 'commit':
            self._conn.commit()
        else:
            self._conn.rollback()

    def dispose(self, isEnd=1):
        """
        @summary: 释放连接池资源
        """
        if isEnd == 1:
            self.end('commit')
        else:
            self.end('rollback')
        self._cursor.close()
        self._conn.close()

'''
if __name__ == '__main__':
    mysql = MyPymysqlPool("dbMysql")
    sqlAll = "select * from userinfo;"
    result = mysql.getAll(sqlAll)
    print(result)
    # 释放资源
    mysql.dispose()
'''

三、操作数据库命令

1、mysql连接

示例

以下是从命令行中连接mysql服务器的简单实例：

C:\Users\gouxr>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

创建数据库

显示数据库名称

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

#创建名为mytest的数据库
mysql> create database mytest;
Query OK, 1 row affected (0.14 sec)
#创建数据表
mysql> create table userinfo (id int auto_increment primary key, name varchar(10) not null, pwd varchar(18) not null);
Query OK, 0 rows affected (0.59 sec)

#查看表结构是否正确
mysql> desc userinfo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
| pwd   | varchar(18) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

#插入3行测试数据
mysql> insert into userinfo (name, pwd) values ("alex", "alex3712"),("mini", "mini1111"),("pax", "pax333");
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from userinfo;
+----+------+----------+
| id | name | pwd      |
+----+------+----------+
|  1 | alex | alex3712 |
|  2 | mini | mini1111 |
|  3 | pax  | pax333   |
+----+------+----------+
3 rows in set (0.00 sec)