使用 python 操作 MySQL 数据库 （查询-删除-插入）

 

使用 python 操作 MySQL 数据库   （查询-删除-插入）

---

1、查询  数据库的表中 是否 有记录；

2、若存在，删除

3、插入 记录

二、代码

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Wed Sep 23 09:12:43 2020

@author: dujidan
"""

import pymysql

def save_list_into_database(database_name, table_name, sample_id, add_info_list):
    sample_id = sample_id_to_accession(sample_id)
    # Connect to the database
    conn_success = False
    retry_counts = 0
    connection = pymysql.connect(host='0.0.0.0',
                                 user='user',
                                 password='pwd',
                                 db=database_name,
                                 cursorclass=pymysql.cursors.DictCursor)
    while not conn_success and retry_counts < 5:
        try:
            with connection.cursor() as cursor:
                sql = 'SELECT SAMPLE_ID FROM `{table_name}` WHERE SAMPLE_ID="{sample_id}" LIMIT 1;'.format(table_name=table_name, sample_id=sample_id)
                n_inquire = cursor.execute(sql)
                if n_inquire == 1:
                    sql = 'DELETE FROM `{table_name}`  WHERE SAMPLE_ID="{sample_id}";'.format(table_name=table_name, sample_id=sample_id)
                    n_get = cursor.execute(sql)
                sql = 'INSERT INTO `{table_name}` VALUES {add_info_list};'.format(table_name=table_name, add_info_list=', '.join(add_info_list))
                # print(sql)
                cursor.execute(sql)
                connection.commit()
                conn_success = True
        except Exception as conn_exp:
            print (conn_exp)
            retry_counts += 1
            sleep(0.5)
    connection.close()
    return []

if __name__ == "__main__":
    save_list_into_database(database_name, table_name, sample_id, add_info_list)

 

 

# 追加 或 更新
sql = "INSERT INTO `sample_information` (`date`, `accession`, `name`, `gender`, `age`) \
                    VALUES {insert_list} ON DUPLICATE KEY UPDATE \
                    date = VALUES(date),\
                    accession = VALUES(accession),\
                    name = VALUES(name),\
                    gender = VALUES(gender),\
                    age = VALUES(age)".format(insert_list = ','.join(insert_list))