python-读写mysql(操作mysql数据库)

最新推荐文章于 2024-07-04 16:04:39 发布

csdn_gyb

最新推荐文章于 2024-07-04 16:04:39 发布

阅读量1.2k

点赞数

分类专栏： python mysql 文章标签： python

本文链接：https://blog.csdn.net/csdn_gyb/article/details/123657482

版权

mysql 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

python

1 篇文章 0 订阅

订阅专栏

import pymysql
import pandas as pd
import time

only_time = time.localtime(time.time())
time_now = time.strftime('%Y-%m-%d %H:%M:%S', only_time)
dt = time.strftime('%Y%m%d', only_time)
t = time.time()
tt = int(t)
parentId = ''
startTime = '2022-01-01'
endTime = '2022-02-28'
#第一种方式获取mysql数据
def load_data_from_mysql(host,port,user,pwd,db_name):
    conn = pymysql.connect(host=host, port=port, user=user, password=pwd, db=db_name, charset="utf8")
    cursor = conn.cursor()
    sql = "select * from dim_common_type limit 2"
    cursor.execute(sql)
    result = cursor.fetchall()
    print(result)
    return result
#第二种方式获取mysql数据
def load_data_from_mysql2(host,port,user,pwd,db_name):
    conn = pymysql.connect(host=host, port=port, user=user, password=pwd, db=db_name, charset="utf8")
    cursor = conn.cursor()
    sql = "select * from dim_common_type limit 3"
    data_farme = pd.read_sql(sql, conn)
    print(data_farme)
    return data_farme
#第一种插入数据
def write_data_to_mysql(host,port,user,pwd,db_name):
    conn = pymysql.connect(host=host, port=port, user=user, password=pwd, db=db_name, charset="utf8")
    cursor = conn.cursor()
    data_list = ["历史", "20220321", time_now, "备注"]
    sql = '''insert into test_tb_name(name, dt,sync_time,aa_info) value ('%s','%s','%s','%s')''' %("历史", "20220321", "20220322", "备注")
    try:
        cursor.execute(sql)
        conn.commit()
    except:
        cursor.rollback()
        print('写入失败')

    cursor.close()
    conn.close()
    return 0
#第二种插入mysql
def write_data_to_mysql2(host,port,user,pwd,db_name):
    try:
        conn = pymysql.connect(host=host, port=port, user=user, password=pwd, db=db_name, charset="utf8")
        cursor = conn.cursor()
        data_list = ["王五", "20220321", time_now, "备注"]
        status = cursor.execute("insert into test_tb_name (name, dt,sync_time,aa_info)"
                            "VALUES (%s,%s,%s,%s)", data_list)
        conn.commit()
    except Exception as e:
        print(time_now, e)
    finally:
        cursor.close()
        conn.close()
        return status

if __name__=="__main__":
    #load_data_from_mysql("localhost", 3306, "user_name", "password", "test")
    #load_data_from_mysql2("localhost", 3306, "user_name", "password", "test")
    write_data_to_mysql2("localhost", 3306, "username", "password", "test")