Python MySQLConnect 代码 存

import pymysql
from itertools import chain

mydb = pymysql.connect(
    host="localhost",  # 数据库主机地址
    user="root",  # 数据库用户名
    passwd="root",  # 数据库密码
    database="studentTest"  # 数据库名
)

cursor = mydb.cursor()


def reconnect(tableName):
    try:
        mydb = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', database='studentTest')
        sql = """ select * from %s """ % tableName
        mydb.ping(reconnect=True)
        cursor.execute(sql)
        mydb.commit()
    except Exception:
        print("Table doesn't exist!")


# table
def searchTable(tableName):
    '''
    查找表有无（表名）
    '''
    reconnect(tableName)
    cursor.execute(""" show tables """)
    tableList = list(chain.from_iterable(cursor.fetchall()))
    try:
        if tableList.index(tableName) < len(tableList):
            print("table find")
            return 1
    except ValueError:
        print("searchTable Error")
        return 0
    mydb.close()


def deleteTable(tableName):
    '''
    删除表(“表名”)
    '''
    try:
        reconnect(tableName)
        sql = """ drop table if exists %s""" % tableName
        cursor.execute(sql)
        mydb.commit()
    except Exception:
        print("delete table error!")
        mydb.rollback()
    mydb.close()


def createSQl(count):
    if count == 1:
        b = {1: 'int not null primary key auto_increment'}
        # 1类：int，非空，主键 自动增长
    elif count == 0:
        b = {0: 'int '}
        # 0类：int
    else:
        b = {
            count: f'varchar({count}) '
            # 其他：varchar （输入长度）
        }
    return b[count]
# sql的数据类型，并没有完全封装，datatime之类的，但是这已经不是个问题了


def createTable(tableName, **kwargs):
    '''
    创建表（"表名"，列1="列1类"，……）
    '''
    reconnect(tableName)
    a = len(kwargs)
    key = []
    values = []
    try:
        for i in kwargs.values():
            values.append(i)
        for i in kwargs.keys():
            key.append(i)
        s = str()
        for i, t in zip(key, values):
            a = (i, createSQl(t))
            b = ' '.join(a)
            b += ','
            s += b
        s = s[:-1]
        s = "(" + s + ")"
        a = f"""create table if not exists {tableName}{s}default charset=utf8;"""
        cursor.execute(a)
        mydb.commit()
    except Exception:
        print('creat table error!A table must have at least 1 column!')
        mydb.rollback()
    mydb.close()


def addTableMessage(tableName, **kwargs):
    '''
    在表中加入数据("表名"，列1="列1数值"，……)
    '''
    reconnect(tableName)
    a = len(kwargs)
    key = []
    values = []
    try:
        for i in kwargs.values():
            values.append(i)
        for i in kwargs.keys():
            key.append(i)
        s = str()
        v = str()
        for i in key:
            b = ''.join(i)
            b += ','
            s += b
        s = s[:-1]
        s = "(" + s + ")"
        for t in values:
            c = "'"+''.join(t)
            c += "',"
            v += c
        v = v[:-1]
        v = "(" + v + ")"
        a = f"""insert into {tableName} {s} values {v};"""
        cursor.execute(a)
        mydb.commit()
        print(a)
    except Exception:
        print('add table message error!')
        mydb.rollback()
    mydb.close()


def deleteTableMessage(tableName, **kwargs):
    '''
    在表中根据数据删除对应行(表名，列1="列1数值"，……)
    '''
    reconnect(tableName)
    a = len(kwargs)
    key = []
    values = []
    try:
        for i in kwargs.values():
            values.append(i)
        for i in kwargs.keys():
            key.append(i)
        s = str()
        for i, t in zip(key, values):
            b = ''.join(i)+"="+'"'+''.join(t)
            b += '" and '
            s += b
        s = s[:-5]
        s = "(" + s + ")"
        a = f"""delete from {tableName} where {s} ;"""
        cursor.execute(a)
        mydb.commit()
        print(a)
    except Exception:
        print('delete table message error!')
        mydb.rollback()
    mydb.close()


def updateTableMessage(tableName, ifname, ifvalue, **kwargs):
    '''
    更新表（“表名”，"需要修改的行的某列","该列值"，列1="列1值"，……）
    '''
    reconnect(tableName)
    a = len(kwargs)
    key = []
    values = []
    try:
        for i in kwargs.values():
            values.append(i)
        for i in kwargs.keys():
            key.append(i)
        s = str()
        v = str()
        for i, t in zip(key, values):
            b = ''.join(i) + "=" + '"' + ''.join(t)
            b += '",'
            s += b
        s = s[:-1]
        v = f"({ifname}='{ifvalue}')"
        a = f"""update {tableName} set {s} where {v};"""
        cursor.execute(a)
        mydb.commit()
        print(a)
    except Exception:
        print('update table message error!')
        mydb.rollback()
    mydb.close()


def searchTableMessage(tableName, **kwargs):
    '''
    查找表中数据（“表名”，列1="列1值",……）
    模糊查找
    '''
    reconnect(tableName)
    a = len(kwargs)
    key = []
    values = []
    try:
        for i in kwargs.values():
            values.append(i)
        for i in kwargs.keys():
            key.append(i)
        s = str()
        for i, t in zip(key, values):
            b = ''.join(i) + " like " + "'%" + ''.join(t)
            b += "%' and "
            s += b
        s = s[:-5]
        s = "(" + s + ")"
        a = f"""select * from {tableName} where {s} ;"""
        cursor.execute(a)
        mydb.commit()
        print(a)
        SqlDomain = cursor.description
        DomainNum = len(SqlDomain)
        print(SqlDomain)
        print(cursor.fetchall())
        cursor.close()
    except Exception:
        print('search table message error!')
        mydb.rollback()
    mydb.close()


# database
def searchDatabase(databaseName):
    '''
    查找库有无
    '''
    cursor.execute(""" show databases """)
    databaseList = list(chain.from_iterable(cursor.fetchall()))
    try:
        if databaseList.index(databaseName) < len(databaseList):
            print("database find")
            return 1
    except ValueError:
        print("searchDatabase Error")
        return 0


def deleteDatabase(databaseName):
    '''
    删除库
    '''
    sql = """ drop table if exists %s""" % databaseName
    cursor.execute(sql)


def createDatabase(databaseName):
    '''
    创建库
    '''
    sql = """ create database %s""" % databaseName
    cursor.execute(sql)

存一下