Python操作Mysql数据库

1. 查询

import pymysql 
# 1. 创建数据库连接（服务器地址，账户，密码，数据库，编码）
con = pymysql.connect(host='localhost', user='root', password='root', database='test', charset='utf8')
# 2. 创建游标
cur = con.cursor()
sql = 'select count(1) from student'
# 3. 执行sql
cur.execute(sql)
# 4. 获取所有记录，返回一个元组
result = cur.fetchall()
print(type(result))
for row in result:
    for x in row:
        print(x,end='')
    print('r')
# 如果涉及增删改，需要提交事务
# con.commit()
# 5. 关闭游标
cur.close()
# 6. 关闭连接
con.close()

2. 插入数据

import pymysql 
# 1. 创建数据库连接（服务器地址，账户，密码，数据库，编码）
con = pymysql.connect(host='localhost', user='root', password='root', database='test', charset='utf8')
# 2. 创建游标
cur = con.cursor()
sql = "insert into student values(9,'李四',22)"
# data = ((7,'陈大文',30),(8,'张三',22))
# 3. 执行sql（插入多行数据）
# cur.executemany(sql,data)
cur.execute(sql)
# 4. 执行sql
con.commit()
# 5. 关闭游标
cur.close()
# 6. 关闭连接
con.close()

3. 批量插入数据（案例：表输入->表输出）

import pymysql 
# 1. 创建数据库连接（服务器地址，账户，密码，数据库，编码）
con = pymysql.connect(host='localhost', user='root', password='root', database='test', charset='utf8')
# 2. 创建游标
cur = con.cursor()
sql = 'select sno,sname,sage,now() from student'
# 3. 执行sql（插入多行数据）
cur.execute(sql)
# 4. 获取结果集
result = cur.fetchall()
print(result)
# 5. 关闭游标
cur.close()
# 6. 关闭连接
con.close()

# 连接别的数据库，清空表再把数据插入
con2 = pymysql.connect(host='localhost', user='root', password='root', database='test2', charset='utf8')
cur2 = con2.cursor()
# 清空数据
sql_del = 'delete from student'
cur2.execute(sql_del)
con2.commit()
# 插入数据
data = result
# print(data)
sql_insert = 'insert into student values(%s,%s,%s,%s)'
cur2.executemany(sql_insert,data)
con2.commit()
# 关闭
cur2.close()
con2.close()

print('数据迁移数据')

 4. 实现表关联（可应用与不同库的数据表之间）

# 实现表关联
import pymysql 
conn = pymysql.connect(host='localhost', user='root', password='root', database='test', charset='utf8')
cur = conn.cursor()
sql = 'select * from score'
cur.execute(sql)
result_score = cur.fetchall()
sql = 'select sno,sname from student'
cur.execute(sql)
result_student = cur.fetchall()
# print(result_score)
# print(result_student)
# 每一行存入字段，所有行存进列表
info_list = []
for row_score in result_score:
    info_dict = {}
    sno = row_score[0]
    cno = row_score[1]
    score = row_score[2]
    for row_student in result_student:
        if row_student[0] == sno:
            name = row_student[1]
    print(str(sno) + ' ' + str(cno) + ' ' + str(score) + ' ' + name)
    info_dict['sno'] = sno
    info_dict['cno'] = cno
    info_dict['score'] = score 
    info_dict['name'] = name 
    info_list.append(info_dict)

# 建表，插入数据
sql_create =  "create table if not exists score2(sno int(11),cno int(11),score int(11),name varchar(50)) charset='utf8' comment = '学生表2'"
cur.execute(sql_create)
sql_truncate =  "truncate table score2"
cur.execute(sql_truncate)
row_count = 1
for i in info_list:
    data = (i['sno'],i['cno'],i['score'],'x')
    print(data)
    sql_insert = 'insert into score2(sno,cno,score,name) values(%s,%s,%s,%s)'%data
    cur.execute(sql_insert)
    conn.commit()
    row_count += 1
    print('已成功插入' + str(row_count) + '行数据')
cur.close()
conn.close()
print('数据迁移成功')