import pymysql
class Oper_Sql:
insert = 'insert into self_user(id,name,sex,age,birthday) values(%s,%s,%s,%s,%s)'
def main():
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='root',
charset='utf8',
db='scott',
cursorclass=pymysql.cursors.DictCursor
)
print(conn)
insert_count = int(input('输入您要插入的总条数: '))
try:
while insert_count > 0:
id = input('请输入id: ')
name = input('亲输入您的姓名: ')
sex = input('请输入您的性别(male or female): ')
age = input('请输入您的年龄: ')
birthday = input('请输入出生日期: ')
with conn.cursor() as cursor:
cursor.execute(query=Oper_Sql.insert, args=[id, name, sex, age, birthday])
for row in cursor.fetchall():
id = row[0]
name = row[1]
sex = row[2]
age = row[3]
birthday = row[4]
print('用户id: %s 用户名: %s 用户性别: %s 用户年龄: %s 用户出生日期: %s' % (id, name, sex, age, birthday))
insert_count -= 1
conn.commit()
except Exception as err:
print(err)
finally:
conn.close()
if __name__ == '__main__':
main()
'''
字符集乱码:
+------+-----------+--------+------+------------+
| id | name | sex | age | birthday |
+------+-----------+--------+------+------------+
| 1001 | aidou | male | 32 | 2020-02-23 |
| 1002 | moli | female | 12 | 2020-03-23 |
| 1003 | ali | male | 32 | 2020-03-04 |
| 1004 | 鐜嬪畯鏂? | male | 19 | 1996-05-04 |
+------+-----------+--------+------+------------+
解决方案:
1. 显示当前字符集
mysql> show variables like 'char%';
2. set character_set_results=gb2312;
mysql> select * from self_user;
+------+--------+--------+------+------------+
| id | name | sex | age | birthday |
+------+--------+--------+------+------------+
| 1001 | aidou | male | 32 | 2020-02-23 |
| 1002 | moli | female | 12 | 2020-03-23 |
| 1003 | ali | male | 32 | 2020-03-04 |
| 1004 | 乱码字符 | male | 19 | 1996-05-04 |
+
'''
insert :
客户端查询结果: