单文件操作数据库
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='',  # 密码

    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('')  # 标志线程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="",

    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="",

    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()




												


																

最新文章
	

    
						
  1. 联合主键用Hibernate注解映射的三种方式
		
    2. 
						
  2. COGS 2437 暗之链锁 II 题解
		
    3. 
						
  3. kafka 生产者java编码
		
    4. 
						
  4. IE9或以上的浏览器flash值为空时，导致domready不触发
		
    5. 
						
  5. Yii2 初体验
		
    6. 
						
  6. main方法执行之前，做什么事
		
    7. 
						
  7. RR区间锁 不是唯一索引,即使区间内没值,也锁
		
    8. 
						
  8. 放大镜效果之js
		
    9. 
						
  9. Effective java -- 2 对于所有对象都通用到方法
		
    10. 
						
  10. springboot 1.5.2 thymeleaf 标签未关闭异常解决办法
		
    11. 
						
  11. SpringMVC+Mybatis 如何配置多个数据源并切换？
		
    12. 
						
  12. Java开发笔记（七十八）面向对象的后门——反射
		
    13. 
						
  13. 6.6 Pandora 实操 - API/SDK
		
    14. 
						
  14. windows安装zookeeper
		
    15. 
						
  15. PAT1103
		
    16. 
						
  16. Linux Shell脚本编程提高(12)
		
    17. 
						
  17. CentOS 7使用yum安装Docker
		
    18. 
						
  18. 精通D3.js笔记
		
    19. 
						
  19. Nginx安装和使用
		
    20. 
						
  20. Mybatis工具Generator
		
    21. 
		

	


热门文章
	

    
									
  1. CF1043F Make It One 容斥+dp+组合
			
    2. 
						
  2. 51 Nod 1020 逆序排列
			
    3. 
						
  3. 【BZOJ2459】 [BeiJing2011]神秘好人
			
    4. 
						
  4. THUSC 2017 游记
			
    5. 
						
  5. （Java多线程系列六）join()的用法和线程的优先级
			
    6. 
						
  6. （Java多线程系列一）快速入门
			
    7. 
						
  7. TCP定时器 之 TIME_WAIT定时器
			
    8. 
						
  8. 二、linux下apache2.2.11+php5.6.3的环境配置
			
    9. 
						
  9. 简易的文件上传 tp5
			
    10. 
						
  10. Spring——原理解析-利用反射和注解模拟IoC的自动装配
			
    11.