Python_24.6.7_mysql插入

import pymysql

# 建立数据库连接
db = pymysql.connect(host='localhost', user='root', password='123456', db='stu')
cursor = db.cursor()

# 创建学生表的SQL语句（注释掉了，因为表如果已存在，则无需重复创建）
sql = '''CREATE TABLE student (sno CHAR(10), sname VARCHAR(20), PRIMARY KEY (sno)) DEFAULT CHARSET=utf8;'''
cursor.execute(sql)

# 修改表结构的SQL语句（同样注释掉，示例用途）
add_sql = '''ALTER TABLE student ADD COLUMN ssex VARCHAR(1), ADD COLUMN sage INT;'''
cursor.execute(add_sql)

# 删除列的SQL语句（示范，根据需要使用）
delete_sql = '''ALTER TABLE student DROP COLUMN sage;'''
cursor.execute(delete_sql)

# 查询并打印当前数据库中的所有表
cursor.execute('SHOW TABLES;')
print(cursor.fetchall())

# 关闭游标和连接（最佳实践是先关闭游标，再关闭连接）
cursor.close()
db.close()

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456', db='stu')
cursor = db.cursor()
# 插入一条数据
# insert_sql='''insert into student values('2022001','ll','male')'''
# cursor.execute(insert_sql)
# 插入多条数据
# insert_many_sql='''insert into student values('2022003','lc','女'),
# ('2022004','xiaomi','男')'''
# cursor.execute(insert_sql)
insert_many_sql = [('230303050', 'lfc', '男'), ('230303051', 'yxj', '男'), ('230303052', 'zwy', '男'),
                   ('230303053', 'yzh', '男')
    , ('230303054', 'cxp', '男'), ('230303055', 'my', '男')]

cursor.executemany('insert into student values(%s,%s,%s);', insert_many_sql)

# 提交到数据库
db.commit()
cursor.execute('select * from student')
print(cursor.fetchall())
cursor.close()