使用pymysql 操作MySQL数据库

---

前言

1、连接数据库
2、在调用其他方法内的 MySQL 语句，用于反复调用；
3、断开连接

---

一、使用步骤

代码如下（示例）：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Jul 17 09:00:59 2020

@author: dujidan
"""

from datetime import date
from time import sleep
import pymysql


class SingleDatabaseConnect(object):
    def __init__(self, schema):
        self.conn_success = False
        self.retry_counts = 0
        self.connect = pymysql.connect(host='xx.xxx.x.xxx', user='user', password='password', 
                                       db=schema, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
        while not self.conn_success and self.retry_counts < 5:
            try:
                self.cursor = self.connect.cursor()
                self.conn_success = True
            except Exception as conn_exp:
                self.retry_counts += 1
                sleep(0.5)

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.cursor:
            self.cursor.close()
        if self.connect:
            self.connect.close()

    def get_values_from_db(self, table, field, parameters):
        """
        :params table
        """
        sql = "SELECT * FROM `{}` WHERE `{}` = '{}'".format(table, field, parameters)
        self.cursor.execute(sql)
        information_list_dict = self.cursor.fetchall()
        return information_list_dict

    def get_batch_values_from_db(self, table, field, parameters):
        """
        :params table
        """
        sql = "SELECT * FROM `{}` WHERE `{}` in {}".format(table, field, parameters)
        self.cursor.execute(sql)
        information_list_dict = self.cursor.fetchall()
        return information_list_dict

    def simple_insert_into_db(self, table, insert_object):
        """
        :params table
        :insert_object 
        """
        sql = "INSERT INTO `{table}` VALUES {insert_object};".format(table=table, insert_object=insert_object)
        self.cursor.execute(sql)
        return self.connect.commit()

    def update_fields_by_accession(self, table_name, saved_object, fields_list, index="accession"):
        """
        update sample's information fields by accession
        make sure saved_object has key: accession!
        """
        if isinstance(saved_object, list):
            set_parameters_clause = ''
            for field in fields_list:
                set_parameters_clause += (',' + field + '=%s')
            update_tuple_list = []
            for each_object in saved_object:
                each_update_values = [each_object[field] for field in fields_list]
                each_update_values.append(each_object[index])
                update_tuple_list.append(tuple(each_update_values))
            update_sql = 'UPDATE {} SET {} WHERE `accession`=%s;'.format(table_name, set_parameters_clause[1:])
            self.cursor.executemany(update_sql, update_tuple_list)
            return self.connect.commit()
        else:
            return -1

    def fast_accession_search(self, table_name, accession, primary_key="accession"):
        """
        """
        search_sql = 'SELECT `{key}` FROM `{table}` WHERE `{key}`="{value}" LIMIT 1;'.format(table=table_name, key=primary_key, value=accession)
        return True if self.cursor.execute(search_sql) else False

    def insert_or_update_fields(self, table_name, saved_object, fields_list, index="accession", date_header_first=False):
        """
        insert this record if one 'accession' not exists and make fields_list is fullfilled !!!
        otherwise update this accession's special fileds
        make sure saved_object has key: accession!
        and --- not batch accession supported ---
        """
        if isinstance(saved_object, dict) and index in saved_object:
            set_parameters_clause = ""
            if self.fast_accession_search(table_name, saved_object[index]):
                for field in fields_list:
                    set_parameters_clause += (',' + field + '="' + saved_object[field] + '"')
                single_sql = 'UPDATE {} SET {} WHERE `accession`="{}";'.format(table_name, set_parameters_clause[1:], saved_object[index])
            else:
                if date_header_first:
                    ordered_insert_fields = '`date`,`' + '`,`'.join(fields_list) + '`'
                    set_parameters_clause = '"' + date.today().strftime("%Y%m%d") + '"'
                else:
                    ordered_insert_fields = '`' + '`,`'.join(field) + '`'
                for field in fields_list:
                    set_parameters_clause += (',"' + saved_object[field] + '"' if set_parameters_clause else '"' + saved_object[field] + '"')
                single_sql = 'INSERT INTO {} ({}) VALUES ({});'.format(table_name, ordered_insert_fields, set_parameters_clause)
            self.cursor.execute(single_sql)
            return self.connect.commit()
        else:
            return -1

if __name__ == '__main__':

    db_access_connect = SingleDatabaseConnect('database')
    db_access_connect.insert_or_update_fields(table_name, saved_object, fields_list, index="accession", date_header_first=True)