主要是工具类,使用pymysql
来创建表与SQL
示例代码
import pymysql
class UseMysql(object):
def __init__(self, user, passwd, db, host="127.0.0.1", port=3306):
self.db = db
self.conn = pymysql.connect(
host=host, user=user, passwd=passwd, db=db, port=port, charset='utf8') # 链接数据库
self.cursor = self.conn.cursor()
def table_exists(self, table_name) -> bool:
"""判断表是否存在
:param table_name: 表名
:return: 存在返回True,不存在返回False
"""
sql = "show tables;"
self.cursor.execute(sql)
tables = self.cursor.fetchall()
for _t in tables:
if table_name == _t[0]:
return True
return False
def create_table(self, data: dict, table_name):
"""创建表"""
# 构造数据库
sql_key_str = ''
columnStyle = ' text' # 数据库字段类型
for key in data.keys():
sql_key_str = sql_key_str + ' ' + key + columnStyle + ','
self.cursor.execute("CREATE TABLE %s (%s)" % (table_name, sql_key_str[:-1]))
# 添加自增ID
self.cursor.execute("""ALTER TABLE `{}` \
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST, \
ADD PRIMARY KEY (`id`);"""
.format(table_name))
# 添加创建时间
self.cursor.execute(
"""ALTER TABLE {} ADD join_time timestamp NULL DEFAULT current_timestamp();""".format(table_name))
def write_dict(self, data: dict, table_name):
"""
写入mysql,如果没有表,创建表
:param data: 字典类型
:param table_name: 表名
:return:
"""
columns = ', '.join("`" + str(x).replace('/', '_') + "`" for x in data.keys())
values = ', '.join("'" + str(x).replace('/', '_') + "'" for x in data.values())
sql = "INSERT INTO %s ( %s ) VALUES ( %s );" % (table_name, columns, values)
self.cursor.execute(sql)
self.conn.commit() # 提交当前事务
if __name__ == '__main__':
mysql = UseMysql('用户名', '密码', '数据库名')
my_data1 = {"col1": "a", "col2": "b", "col3": "c", }
mysql.write_dict(my_data1, table_name="mytable")
my_data2 = {"col1": "a2", "col2": "b2"}
mysql.write_dict(my_data2, table_name="mytable")