import MySQLdb
#打开数据库连接。localhost为主机地址,root是mysql登陆名,123是登陆密码,pythondb是数据库名
conn = MySQLdb.connect("192.168.2.11","dong","123456","pythondb",charset='utf8')
#使用cursor()方法获取操作游标,利用游标来进行相关的数据操作
cursor = conn.cursor()
###########创建列表##########
#创建行为列表 ####设置id自动增加auto_increment,还得指出主键
create_sql = "create table behavior(id int(5) NOT NULL auto_increment,behavior_name varchar(20) NOT NULL,PRIMARY KEY (`id`))"
cursor.execute(create_sql)
#写入行为表
sql1 = """INSERT INTO behavior(behavior_name) VALUES ("回答"),("举手"),("写字"),("睡觉"),("听讲"),("打电话")"""
try:
# 执行sql语句
cursor.execute(sql1)
# 提交到数据库执行
conn.commit()
except:
# Rollback in case there is any error
print("Error: unable to insert data")
conn.rollback()
###
#创建用户列表
create_sql1 = "create table user(id int(5) NOT NULL auto_increment,student_name varchar(20) NOT NULL,user_number varchar(20) NOT NULL,sex varchar(20) NOT NULL, PRIMARY KEY (`id`))"
cursor.execute(create_sql1)
#写入用户表
sql2 = """INSERT INTO user(student_name,user_number,sex) VALUES ("小明","2010","男"),('小白','2011','女'),('小花','2012','女'),('小李','2013','男'),('小华','2014','男'),('大白','2015','女')"""
try:
# 执行sql语句
cursor.execute(sql2)
# 提交到数据库执行