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")
python-读写mysql(操作mysql数据库)
最新推荐文章于 2024-08-19 10:52:43 发布