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)
python+mysql 数据添加、查找、删除和pandas表格存入sql
最新推荐文章于 2022-09-17 12:16:35 发布