1、创建数据库表
def create_table(tb_name):
import pymysql
db = pymysql.Connect('localhost','root','123','zabbix_db')
cursor = db.cursor()
sql = 'create table %s(id int,name char(30),age int);'%tb_name
cursor.execute(sql)
cursor.close()
db.close()
create_table(user)
2、添加数据
def insert_data(id_data,name_data,age_data):
import pymysql
db = pymysql.Connect('127.0.0.1','root','123','zabbix_db')
cursor = db.cursor()
sql = 'insert into user1(id,name,age) values(%s,%s,%s);'
cursor.execute(sql,(id_data,name_data,age_data))
db.commit()
cursor.close()
db.close()
print('执行成功,数据库连接关闭')
insert_data(5,"James",33)
3、删除数据
def del_data(name_data):
import pymysql
db = pymysql.Connect('127.0.0.1', 'root', '123', 'zabbix_db')
cursor = db.cursor()
sql = 'delete from user1 where name=%s;'
cursor.execute(sql, (name_data))
db.commit()
cursor.close()
db.close()
print('执行成功,数据库连接关闭')
del_data('Luckly')
4、更新数据
def update_data(new_age,old_name):
import pymysql
db = pymysql.Connect('127.0.0.1', 'root', '123', 'zabbix_db')
cursor = db.cursor()
sql = 'update user1 set age=%s where name=%s;'
cursor.execute(sql, (new_age,old_name))
db.commit()
cursor.close()
db.close()
print('执行成功,数据库连接关闭')
update_data(18,'tom')
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/bb3135ab3d81c394ccced3ba6132d9fe.png)
5、查询数据
def search_data():
db = pymysql.connect('127.0.0.1','root','123','zabbix_db')
cursor = db.cursor()
sql = 'select * from user1'
cursor.execute(sql)
all_users = cursor.fetchall()
i = 0
while i < len(all_users):
user = all_users[i]
print('name:{}\tage:{}'.format(user[1],user[2]))
print('-----'*10)
i += 1
cursor.close()
db.close()