python 增删改查数据库封装_python对数据库进行增删改查

一.创建一个数据库

import pymysql

try:

conn = pymysql.connect('localhost',user='root',password='123456')

cur = conn.cursor()

cur.execute('create database python01')

cur.close()

conn.close()

except  Exception as result:

print(result)

二.创建一张数据库表

import pymysql

try:

conn = pymysql.connect('localhost',user='root',password='123456',db='python01',port=3306,charset='utf8')

cur = conn.cursor()

sql_create ="""

create table student(

stuid INTNOT NULL primary key,

stuname varchar(20) not null,

stuage  INTNOT NULL );

"""

cur.execute(sql_create)

cur.close()

conn.close()

except  Exception as result:

print(result)

三.向表中添加数据

import pymysql

''''

插入数据'''

try:

conn = pymysql.connect('localhost',user='root',password='123456',db='python01',port=3306,charset='utf8')

cur = conn.cursor()

sql_insert='''

insert into student values (1,'张三',18)

'''

sql_insert2='''

insert into student values (2,'李四',19)

'''

cur.execute(sql_insert)

cur.execute(sql_insert2)

conn.commit()###要提交事务

cur.close()

conn.close()

except  Exception as result:

print(result)

四.列出表中数据

import pymysql

''''

查询数据'''

try:

conn = pymysql.connect('localhost',user='root',password='123456',db='python01',port=3306,charset='utf8')

cur = conn.cursor()

sql_select='''

select *from student

'''

cur.execute(sql_select)

print(cur.fetchall())

cur.close()

conn.close()

except  Exception as result:

print(result)

五.修改表中数据

import pymysql

''''

更新数据'''

try:

conn = pymysql.connect('localhost',user='root',password='123456',db='python01',port=3306,charset='utf8')

cur = conn.cursor()

sql_update='''

update student set stuname='赵云' where stuid=1

'''

cur.execute(sql_update)

conn.commit()

cur.close()

conn.close()

except  Exception as result:

print(result)

六.删除表中数据

import pymysql

''''

删除数据'''

try:

conn = pymysql.connect('localhost',user='root',password='123456',db='python01',port=3306,charset='utf8')

cur = conn.cursor()

sql_delete='''

delete from student  where stuid=1

'''

cur.execute(sql_delete)

conn.commit()

cur.close()

conn.close()

except  Exception as result:

print(result)