Python每日练习之操作Mysql数据库

1、查询

import pymysql as pymysql
# 打开数据库连接
conn = pymysql.connect(host="IP",user="root",password="pw",database="test_h", charset="utf8")
# 使用cursor()方法获取操作游标
cur = conn.cursor()
# 编写sql查询语句
sql = "select * from test_h.test_h"
# 执行sql语句
cur.execute(sql)
# 获取查询结果
results = cur.fetchall()
print(results)
# 格式化输出结果，增加表头
print("id","name")
for row in results:
    id = row[0]
    name = row[1]
    print(id,name)
cur.close()
conn.close()

2、 普通插入

import pymysql as pymysql
# 打开数据库连接
conn = pymysql.connect(host="66.1.30.9",user="root",password="0gvzJr66iNs5",database="test_h", charset="utf8")
cur = conn.cursor()
# SQL插入
sql = "insert into test_h(id,name) VALUES(2,'wangkuan')"
try:
    # 执行sql
    cur.execute(sql)
    conn.commit()
except:
    conn.rollback()
cur.close()
conn.close()

3、参数插入

import pymysql as pymysql
# 打开数据库连接
conn = pymysql.connect(host="66.1.30.9",user="root",password="0gvzJr66iNs5",database="test_h", charset="utf8")
cur = conn.cursor()
# SQL参数插入
values = (4,"wangkuan")
sql1 = "insert into test_h values(%s,%s)"
try:
    cur.execute(sql1,values)
    conn.commit()
except:
    conn.rollback()
    print("error")
cur.close()
conn.close()

4、批量循环插入

import pymysql as pymysql
# 打开数据库连接
conn = pymysql.connect(host="66.1.30.9",user="root",password="0gvzJr66iNs5",database="test_h", charset="utf8")
cur = conn.cursor()
# SQL参数插入
sql1 = "insert into test_h(id,name) values(%s,%s)"
str = 'wangkuan chenlou duhaoyang cuixiaochun wangyu mixu'
for i in range(1,5):
    for j in str:
        row_count = cur.execute(sql1,[i,j])
        print("执行",i,"条语句！")
conn.commit()
cur.close()
conn.close()

4’、批量循环插入***

import pymysql as pymysql
import string,random
# 打开数据库连接
conn = pymysql.connect(host="66.1.30.9",user="root",password="0gvzJr66iNs5",database="test_h", charset="utf8")
cur = conn.cursor()
# 循环插入随机数据
sql = "insert into test_h values(%s,%s)"
words = list(string.ascii_letters)  #ascii_letters生成全部字母，包括a-z,A-Z
for i in range(20):
    random.shuffle(words)  # 将列表打乱顺序
    print(words)
    cur.execute(sql,(i+1,"".join(words[0:5])))   #"".join(words[:5]) 一句为将列表words中的第0~4位元素连接起来
conn.commit()
cur.close()
conn.close()

5、更新

> import pymysql as pymysql
# 打开数据库连接
conn = pymysql.connect(host="66.1.30.9",user="root",password="0gvzJr66iNs5",database="test_h", charset="utf8")
cur = conn.cursor()
# 删除SQL
sql = "update test_h set id=2 where name='wangkuan'"
cur.execute(sql)
conn.commit()
cur.close()
conn.close()

6、删除

import pymysql as pymysql
# 打开数据库连接
conn = pymysql.connect(host="66.1.30.9",user="root",password="0gvzJr66iNs5",database="test_h", charset="utf8")
cur = conn.cursor()
# 删除SQL
sql = "delete from test_h where id=2"
cur.execute(sql)
conn.commit()
cur.close()
conn.close()