python_操作MySQL 初解

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