import pandas as pd
import pymysql
import datetime
class DBHelper:
def __init__(self, dbName, env):
if env == "dev":
self.host = "192.168.1.12"
self.port = 3306
self.user = "root"
self.password = "Password123"
self.name = dbName
else:
self.host = "clod-test"
self.port = 3306
self.user = "cld"
self.password = "passowrd"
self.name = dbName
def db_config(self):
config = {
'host': self.host,
'port': self.port,
'user': self.user,
'password': self.password,
'db': self.name,
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.DictCursor,
}
return config
def connectionbase(self, config):
connection = pymysql.connect(**config)
return connection
def to_mysql(db, env, data):
db = DBHelper(db, env)
connection = db.connectionbase(db.db_config())
sql = "INSERT INTO library(question,answer,create_time) VALUES(%s,%s,%s)"
try:
with connection.cursor() as cursor:
cursor.executemany(sql, data)
connection.commit()
cursor.close()
print("insert to mysql successfull!")
except Exception as e:
connection.rollback()
print(e)
finally:
connection.close()
df = pd.read_csv(r"F:\data\data\mongodb\query.csv", sep=",", encoding="utf8")
result = []
for idx, row in df.iterrows():
question = row["question"].replace('"', "").replace("\n", "")
answer = row["answer"].replace('"', "").replace("\n", "")
combine_line = (question, answer, str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
result.append(combine_line)
print("total data:{}".format(len(result)))
to_mysql("test", "dev", result)
