python mysql API

朴灿烈づ我的快乐病毒、 2022-01-11 05:29 325阅读 0赞

### **一、环境准备** ###

*  mysql安装 [https://mp.csdn.net/postedit/81771687][https_mp.csdn.net_postedit_81771687]
 *  下载依赖包 pip3 install pymysql

### **二、接口调用** ###

**以下罗列两种实现方式，**

**第一种：是原生的；**

**第二种：集成pandas的，原理是ORM对象关系映射技术，即ORM（Object-Relational Mapping）技术，指的是把关系数据库的表结构映射到对象上，通过使用描述对象和数据库之间映射的元数据，将程序中的对象自动持久化到关系数据库中。用起来很方便，就跟java的Hibernate和mybatis一样。**

**第一种：**

*  configuration.properties

[SQL]
    sql_host:172.8.10.xx
    sql_port:3306
    sql_db:xx
    sql_username:xx
    sql_password:xx

*  MysqlClient

#!/usr/bin/python3
    # -*- coding: UTF-8 -*-
    
    import json
    import pymysql
    
    config=ConfigParser()
    config.read(str(config_file['configPath'].iloc[0]))
    
    # =============================================================================
    # initialization
    # =============================================================================
    
    SQL_host = config.get('SQL', 'SQL_host')
    SQL_port = config.getint('SQL', 'SQL_port')
    SQL_username = config.get('SQL', 'SQL_username')
    SQL_password = config.get('SQL', 'SQL_password')
    SQL_db = config.get('SQL', 'SQL_db')
    
    class MysqlClient:
    
        def __init__(self):
    
            self.connection = pymysql.Connect(host=SQL_host, port=SQL_port,
                                              user=SQL_username, passwd=SQL_password,
                                              db=SQL_db, charset='utf8')
            self.cursor = self.connection.cursor()
    
        def insert(self, query):
            try:
                self.cursor.execute(query)
                self.connection.commit()
            except Exception as e:
                # self.connection.rollback()
                raise Exception(e)
            finally:
                self.cursor.close()
                self.connection.close()
    
        def query(self, query):
            try:
                cursor = self.connection.cursor(pymysql.cursors.DictCursor)
                cursor.execute(query)
                return cursor.fetchall()
            except Exception as e:
                raise Exception(e)
            finally:
                self.cursor.close()
                self.connection.close()

**第二种：**

下载依赖包

*  pip3 install pandas
 *  pip3 install sqlalchemy
 *  pip3 install pymysql

其中，pandas模块提供了read\_sql\_query（）函数实现了对数据库的查询，to\_sql（）函数实现了对数据库的写入。并不需要实现新建MySQL数据表。

sqlalchemy模块实现了与不同数据库的连接，而pymysql模块则使得Python能够操作MySQL数据库。

#!/usr/bin/python3
    # -*- coding: UTF-8 -*-
    
    import json
    import pandas as pd
    import sqlalchemy
    import pymysql
    
    conn_Mysql = create_engine("mysql+pymysql://{SQL_username}:{SQL_password}@{SQL_host}:{SQL_port}/{SQL_db}".format(
        SQL_username=SQL_username, SQL_password=SQL_password,
        SQL_host=SQL_host, SQL_port=SQL_port, SQL_db=SQL_db))

案例：

sql_query = '''select alarm_time from xx
            where machine_id={mid} and spindle_name={sid}
            and cutter_location_name={tn} and program_no="{pn}"
            '''.format(mid=machineID, sid=spindleID, tn=toolNum, pn=programNum)
    
            histAlarmTime = pd.read_sql(sql_query, conn_Mysql)

[https_mp.csdn.net_postedit_81771687]: https://mp.csdn.net/postedit/81771687