python连接数据库实现增删改查

python连接数据库实现增删改查

import pymysql

conn = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8")

print("---创建cursor对象，查询数据库---")

cur=conn.cursor()

r=cur.execute("show databases")

print(r)

dbs=cur.fetchall()

print(dbs)

for i in dbs:

print(i[0])

print("---创建数据库---")

# cur.execute("create database jing_dong charset=UTF8MB4")

print("---使用数据库---")

cur.execute("use jing_dong")

print("创建表：")

cur.execute("create table goods(\

id int unsigned primary key auto_increment not null,\

name varchar(150) not null,\

cate_name varchar(40) not null,\

brand_name varchar(40) not null,\

price decimal(10,3) not null default 0\

)")

print("---查询表---")

cur.execute("show tables")

tables=cur.fetchall()

print(tables)

for i in tables:

print(i[0])

print("---操作表中的数据---")

print("1.向goods表中插入数据：")

cur.execute("insert into goods values(1,'r510vc 15.6英寸笔记本','笔记本','华硕','3399'),(2,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999')")

print("2.查询数据：")

count=cur.execute("select * from goods")

print(count)

dbs=cur.fetchall()

print(dbs)

print("3.修改数据：")

cur.execute("update goods set price='1999' WHERE price='4999'")

print("4.删除数据：")

cur.execute("delete from goods WHERE price='1999'")

count=cur.execute("select * from goods")

print(count)

dbs=cur.fetchall()

print(dbs)

conn.commit()

cur.close()

conn.close()

---