python+mysql 数据添加、查找、删除和pandas表格存入sql

最新推荐文章于 2022-09-17 12:16:35 发布

四小馒头

最新推荐文章于 2022-09-17 12:16:35 发布

阅读量545

点赞数

分类专栏：爬虫文章标签： sql python 数据库

本文链接：https://blog.csdn.net/qq_43533416/article/details/125073348

版权

爬虫专栏收录该内容

1 篇文章 0 订阅

订阅专栏

import pymysql

import sqlalchemy

HOST = '127.0.0.1'
PORT = 3306
USER = 'root'
PWD = 'xxx'
CHARSET = 'utf8mb4'


def insert(db, tablename, values, _charset=CHARSET):
    conn = pymysql.connect(host=HOST,
                           port=PORT,
                           user=USER,
                           passwd=PWD,
                           db=db,
                           charset=_charset
                           )
    cursor = conn.cursor()  # 游标对象，用于执行查询和获取结果
    sql = 'insert into ' + db + '.' + tablename + ' ('
    temp = ''
    for key in values.keys():
        sql += key + ' ,'
        value = values[key]
        if type(value) == type(""):
            value = value.replace('\'', '"').replace('\\', '\\\\')
            temp += '\'' + value + '\' ,'
        else:
            temp += str(value) + ' ,'
    sql = sql[:-1] + ') values (' + temp[:-1] + ')'
    # print(sql)

    cursor.execute(sql)
    conn.commit()
    # print("记录插入成功！")
    conn.close()


def select(db, tablename, condition=None, _charset=CHARSET):
    conn = pymysql.connect(host=HOST,
                           port=PORT,
                           user=USER,
                           passwd=PWD,
                           db=db,
                           charset=_charset
                           )
    cursor = conn.cursor()  # 游标对象，用于执行查询和获取结果
    sql = 'select * from ' + db + '.' + tablename
    if condition:
        sql += ' where'
        for key in condition.keys():
            value = condition[key]
            if type(value) == type(""):
                value = value.replace('\'', '"').replace('\\', '\\\\')
                value = '\'' + value + '\''
            else:
                value = str(value)
            sql += ' ' + key + ' = ' + value + ' and'
        sql = sql[:-3]
    # print(sql)
    cursor.execute(sql)
    result = cursor.fetchall()
    conn.commit()
    conn.close()
    return result


def update(db, tablename, values, condition, _charset=CHARSET):
    conn = pymysql.connect(host=HOST,
                           port=PORT,
                           user=USER,
                           passwd=PWD,
                           db=db,
                           charset=_charset
                           )
    cursor = conn.cursor()  # 游标对象，用于执行查询和获取结果
    sql = 'update ' + db + '.' + tablename + ' set '
    for key in values.keys():
        if key in condition:
            continue
        value = values[key]
        if type(value) == type(""):
            value = value.replace('\'', '"').replace('\\', '\\\\')
            value = '\'' + value + '\''
        else:
            value = str(value)
        sql += ' ' + key + ' = ' + value + ' ,'
        # update spark.challenge set xx = x , xx = x where xx = x  中前面set是用, 不是and连接
    sql = sql[:-1] + ' where '
    for key in condition.keys():
        value = condition[key]
        if type(value) == type(""):
            value = value.replace('\'', '"').replace('\\', '\\\\')
            value = '\'' + value + '\''
        else:
            value = str(value)
        sql += ' ' + key + ' = ' + value + ' and'
    sql = sql[:-3] + ';'
    print(sql)
    cursor.execute(sql)
    conn.commit()
    conn.close()


def delete(db, tablename, condition, _charset=CHARSET):
    conn = pymysql.connect(host=HOST,
                           port=PORT,
                           user=USER,
                           passwd=PWD,
                           db=db,
                           charset=_charset
                           )
    cursor = conn.cursor()  # 游标对象，用于执行查询和获取结果
    sql = 'delete from ' + db + '.' + tablename + ' where '

    for key in condition.keys():
        value = condition[key]
        if type(value) == type(""):
            value = value.replace('\'', '"').replace('\\', '\\\\')
            value = '\'' + value + '\''
        else:
            value = str(value)
        sql += ' ' + key + ' = ' + value + ' and'
    sql = sql[:-3]
    # print(sql)
    cursor.execute(sql)
    conn.commit()
    conn.close()


def executeSql(db, sql_string, _charset=CHARSET):
    conn = pymysql.connect(host=HOST,
                           port=PORT,
                           user=USER,
                           passwd=PWD,
                           db=db,
                           charset=_charset
                           )
    cursor = conn.cursor()  # 游标对象，用于执行查询和获取结果
    cursor.execute(sql_string)
    result = None
    if sql_string.find('select') > -1 or sql_string.find('show') > -1:
        result = cursor.fetchall()
    conn.commit()
    conn.close()
    return result

def pd_to_sql(data, database, table_name, if_index=False, if_exists='replace', dtype=None):
    # if_exists='append'则为如果表存在则新数据扩展而不是替换表
    engine = sqlalchemy.create_engine('mysql+pymysql://' + USER + ':' + PWD + '@' + HOST + ':' + str(PORT) + '/' + database)
    if dtype:
        data.to_sql(table_name, engine, index=if_index, if_exists=if_exists,
                 dtype=dtype)
    else:
        data.to_sql(table_name, engine, index=if_index, if_exists=if_exists)
    print('the dataframe has been written into mysql table %s successfully!' % table_name)