import pymysql
MYSQL_CONFIG = {
'host': 'localhost', # IP地址
'port': 3306, # 端口
'user': 'root', # 用户名
'passwd': 'mysql', # 密码
'db': 'test03', # 数据库
'charset': 'utf8', # 编码
}
def generate_sql(tb, key_list):
cols = ", ".join('`{}`'.format(k) for k in key_list)
val_cols = ', '.join('%({})s'.format(k) for k in key_list)
sql = """
INSERT INTO %s(%s) VALUES(%s)
""" % (tb, cols, val_cols)
return sql
def generate_sql2(tb, key_list, conditions=None):
cols = ", ".join('`{}`=%({})s'.format(k, k) for k in key_list)
print(cols)
if not conditions:
conditions = 'where id=%(id)s'
sql = """
update %s set %s %s
""" % (tb, cols, conditions)
return sql
def main():
student_list1 = [
{'name': 'yyy', 'age': 18, 'sex': 'female'},
{'name': 'zzz', 'sex': 'male', 'age': None}
]
student_list = [
{'name': 'lkjh', 'age': 9, 'sex': '', 'id': 19},
]
conn = pymysql.connect(**MYSQL_CONFIG) # 数据库连接
cur = conn.cursor() # 游标对象
sql = generate_sql2('student', ['name', 'sex', 'age'])
sql2 = generate_sql('student', ['name', 'sex', 'age'])
print(sql)
cur.executemany(sql, student_list)
# cur.executemany(sql2, student_list1)
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
main()