python_操作MySQL 初解

单文件操作数据库

import random
import threading, multiprocessing
import time, datetime
import pymysql
import queue

con = pymysql.Connect(
    host='127.0.0.1',  # IP配置
    port=3306,  # mysql端口号
    user='root',  # 用户名
    passwd='1234',  # 密码
    db='test',  # 连接数据库
    charset='utf8'  # 编码设置
)
cur = con.cursor()  # 游标创建

d = {
    "create": 'create table if not exists day1(card_id int, name VARCHAR(30), address VARCHAR(128)) default charset=utf8',
    # 创建表
    "insert": ['insert into day1(card_id,name,address) values(1,"Rose","北京")',  # 插入数据
               'insert into day1(card_id,name,address) values(2,"Jack","天津")',
               'insert into day1(card_id,name,address) values(3,"张三","合肥")',
               'insert into day1(card_id,name,address) values(4,"LiLy","河北")', ],
    "select": 'select * from day1',
    "drop": 'drop table if EXISTS day1',
}


def for_out(cur):  # 通过游标遍历将数据拿出
    for row in cur:
        print(row)
    print()


try:
    print("初始化数据库")
    print(d["drop"])
    cur.execute(d["drop"])
    print("开始创建表")
    cur.execute(d["create"])
    print("开始插入数据")
    for i in d["insert"]:
        print(i)
        cur.execute(i)
    a = datetime.datetime.now()
    print(a)
    time.sleep(3)
    print("执行查询操作")
    # for_out(cur)
    b = datetime.datetime.now()


except Exception as f:
    print(f)
    con.rollback()
q = queue.Queue()  # 创建一个消息队列用来承接SQL语句


def for_1():
    q.put(cur.execute(d["select"]))  # 把查询到的结果放入消息队列
    for_out(cur)  # 查询放入的东西
    print('********')  # 标志线程1
    print(b - a)  # 打印运行时间


def for_2():
    print('123')  # 标志线程2
    while True:
        q.get()  # 从消息队列中拿出之前放入的东西


if __name__ == '__main__':
    t1 = threading.Thread(target=for_1)
    t2 = threading.Thread(target=for_2)
    t1.start()
    t2.start()
    t1.join()
    t2.join()

cur.close()
con.close()

# a = datetime.datetime.now()
# time.sleep(3)
# b = datetime.datetime.now()
# print(b-a)
# a1=time.time()
# time.sleep(3)
# b2 = time.time()
# print(b2-a1)

# x = list(map(str,[1,2,3])
# )
# print(x)
# print(1,2,3,sep=',')
# print(True*3)

# def sushu(name):
#     print("函数功能")
#     time.sleep(1)
#     # print(name)
#     print("函数名是%s" % threading.currentThread().name)
# l=[]
# Pool=[]
# for i in range(4):
#     t = threading.Thread(target=sushu,name="%d线程"%i)
#     l.append(t)
#
# for i in l:
#     i.start()
#
# for i in l:
#     i.join()

#
# t1 =threading.Thread(target=sushu,args=(1,))
# t2 =threading.Thread(target=sushu,args=(2,))
# t3 =threading.Thread(target=sushu,args=(3,))
# t4 =threading.Thread(target=sushu,args=(4,))
#
# t1.start()
# # t1.join()
# t2.start()
# # t2.join()
# t3.start()
# # t3.join()
# t4.start()
# # t4.join()
# print("函数名是%s  运行了%d个线程  当前时间%s" % (threading.currentThread().name, threading.active_count(), time.ctime()))
# t1.join()
# t2.join()
# t3.join()
# t4.join()


# a = random.randint(1, 20) * 100
# print(a)

# -*- coding: utf-8 -*-
# __author__ = "Allen"

import pymysql
con=pymysql.connect(
    host="localhost",
    user="root",
    port=3306,
    passwd="123",
    charset="utf8",
    db="test"
)
cur=con.cursor()
sql={
    "create":'create table if not  exists shu(name char(15)not null,sex char(10),age int,score int)default charset="utf8"',
    "insert":[
        'insert into shu values("小王","男",26,97)',
        'insert into shu values("小白","女",34,89)',
        'insert into shu values("小黄","女",22,95)',
        'insert into shu values("小黑","女",27,58)',
        'insert into shu values("小刘","男",24,76)',
        'insert into shu values("小讲","男",34,65)',
        'insert into shu values("小班","男",42,79)'
    ],
    "select":'select * from shu where sex="男";',
    "select1":'select * from shu where sex="女";',
    "select2":'select * from shu where score>80;',
    "update":'update shu set age=age+2;',
    "select3":'select * from shu;',
    "delete":'delete from shu where name="小讲";',
    "select4":'select * from shu order by age desc;',
    "select5":'select * from shu order by age asc;',
    "drop":'drop table if exists shu',
}
def for_out(cur):
    for i in cur:
        print(i)
    print( )
try:
    print("初始化数据库")
    print(sql["create"])
    cur.execute(sql["create"])

    for j in sql["insert"]:
        print(j)
        cur.execute(j)
    cur.execute(sql["select"])
    for_out(cur)

    cur.execute(sql["select1"])
    for_out(cur)

    cur.execute(sql["select2"])
    for_out(cur)

    cur.execute(sql["update"])
    for_out(cur)
    cur.execute(sql["select3"])
    for_out(cur)
    cur.execute(sql["delete"])
    for_out(cur)
    cur.execute(sql["select4"])
    for_out(cur)
    cur.execute(sql["select5"])
    for_out(cur)
    cur.execute(sql["drop"])
    for_out(cur)
except Exception as f:
    print(f)
    con.rollback()
con.close()
cur.close()

 单文件操作MySQL

import pymysql
db=pymysql.connect(
    host="localhost",
    port=3306,
    database="test",
    user="root",
    passwd="123",
    charset="utf8"
)

#设置游标
cr=db.cursor()
sql_gn={"insert1":"insert commodity values(0,'iPhone6',2100,4.9,99,'富士康'),"
                  "(0,'iPhone6s',2900,4.9,101,'硅谷'),"
                  "(0,'iPhone6plus',3100,5.5,131,'上海'),"
                  "(0,'iPhone6sPlus',3400,5.5,150,'北京'),"
                  "(0,'iPhone7',3900,4.9,161,'天津'),"
                  "(0,'iPhone7Plus',4100,5.5,180,'重庆'),"
                  "(0,'iPhone8',4300,4.9,191,'四川'),"
                  "(0,'iPhone8Plus',4700,5.5,210,'沈阳'),"
                  "(0,'iPhoneX',5200,4.9,230,'广州'),"
                  "(0,'苹果',10.5,2,200,'富士山'),"
                  "(0,'梨',5.5,2.5,210,'烟台'),"
                  "(0,'香蕉',7.5,3,193,'泰安'),"
                  "(0,'荔枝',235,5,213,'贵州'),"
                  "(0,'核桃',25.5,0.7,217,'邯郸'),"
                  "(0,'柚子',11,6.2,219,'广西'),"
                  "(0,'西瓜',2.5,6.7,220,'郑州'),"
                  "(0,'葡萄',7.9,0.3,230,'大理'),"
                  "(0,'提子',5.5,0.4,231,'濮阳'),"
                  "(0,'哈密瓜',30,4.8,130,'南海'),"
                  "(0,'椰子',35,4.6,121,'安阳'),"
                  "(0,'猕猴桃',21,2.1,131,'焦作'),"
                  "(0,'桃子',2.7,3.1,232,'大名'),"
                  "(0,'杏',2.2,1.7,233,'邢台'),"
                  "(0,'芒果',21,3.2,234,'永年'),"
                  "(0,'点心',35,2.3,3099,'石家庄'),"
                  "(0,'玉米',0.7,0.6,235,'沧州'),"
                  "(0,'无线鼠标',170,1.3,234,'廊坊'),"
                  "(0,'无线键盘',190,7.8,237,'霸州'),"
                  "(0,'三星显示器',7100,52,238,'湖南'),"
                  "(0,'小米耳机',149,1.2,239,'湖北');",
        "delete1":"delete from commodity where ID%2=0;",
        "select1":"select * from commodity where price<20;",
        "update1":"update commodity set price=70 where price<20;",
        "update2":"update commodity set price=1 where price=30;",
        "delete2":"delete from commodity where ID=11 or ID=13;",
        "update3":"update commodity set price=price*0.7",
        "insert2":"insert commodity values(0,'手机',2300,5.5,199,'杭州'),"
                  "(0,'华为',3100,4.5,102,'中关村');",
        "select2":"select * from commodity where goods_name='手机';",
        "select3":"select count(*) from commodity;",
        "delete3":"delete from commodity where ads='湖南' or ads='湖北';",
        "update4":"update commodity set price=price*1.3 where goods_name like '%点心%' and price<100;",
        "update5":"update commodity set goods_name=replace(goods_name,'手机','移动电话');"}
try:
    cr.execute(sql_gn["insert1"])#插入30条记录
    cr.execute(sql_gn["delete1"])#删除ID是偶数的商品
    cr.execute(sql_gn["update2"])#把所有价格为30的商品库存修改为1
    cr.execute(sql_gn["delete2"])#删除id是11，13的记录
    cr.execute(sql_gn["update3"])#所有商品价格减少百分之30
    cr.execute(sql_gn["delete3"])#删除产地是“湖南”,”湖北”的商品
    cr.execute(sql_gn["update4"])  # 把含有‘点心’的商品价格上调百分之30如果价格超过100元则该商品不上调
    cr.execute(sql_gn["select1"])  # 先查询商品价格小于20的记录
    x = cr.fetchall()
    print(x)
    cr.execute(sql_gn["update1"])  # 这部分的商品价格改为70
    cr.execute(sql_gn["insert2"])  # 添加2个新的商品
    cr.execute(sql_gn["select2"])  # 显示商品名中包含’手机’两字的商品
    x1 = cr.fetchall()
    print(x1)
    cr.execute(sql_gn["update5"])  # 将手机改为移动电话
    cr.execute(sql_gn["select3"])
    x2=cr.fetchall()
    print(x2)
    cr.execute(sql_gn["update4"])
except Exception as ex:
    print("写入异常")
    db.rollback()
    cr.close()
    db.close()
    print(ex)
else:
    db.commit()
    cr.close()
    db.close()