# coding=utf-8
"""
作者:gaojs
功能:
新增功能:
日期:2022/4/17 13:59
"""
import pymysql
class Database:
def __init__(self):
# 连接数据库
self.connect = pymysql.Connect(
host='localhost',
port=3306,
user='root',
passwd='admin',
db='gaojs_test',
charset='UTF8'
)
# 获取游标
self.cursor = self.connect.cursor()
self.cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.
data = self.cursor.fetchone()
print("Database version : %s " % data)
def create_table(self):
"""
创建表
:return:
"""
# 切换数据库
self.cursor.execute("use gaojs_test;")
# 创建表语句
self.cursor.execute("DROP TABLE IF EXISTS tb_gjs")
self.cursor.execute("DROP TABLE IF EXISTS gaojs")
# 使用预处理语句创建表
sql = "CREATE TABLE tb_gjs( number char(10), name varchar(50), age int, gender varchar(10) , salary char(20));"
sql1 = "CREATE TABLE gaojs(sid char(15), yuwen char(10), shuxue varchar(50), english int, physics varchar(10) , history char(20), computer char(20));"
self.cursor.execute(sql)
self.cursor.execute(sql1)
self.cursor.execute("desc tb_gjs;")
self.connect.commit()
def insert_sql(self):
"""
update插入数据: UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
:return:
"""
# self.cursor.execute("update tb_gjs set phone='13666666666' where name='qiubing';")
# self.cursor.execute("update tb_gjs set phone='13688888888' where name='gaojianshuai';")
self.cursor.execute("INSERT INTO tb_gjs (number, name, age, gender, salary) VALUE ('001', 'gaojianshuai', 30, 'man', '16000')")
self.cursor.execute("INSERT INTO tb_gjs (number, name, age, gender, salary) VALUE ('002', 'chensishi', 27, 'woman', '18000')")
self.cursor.execute("INSERT INTO tb_gjs (number, name, age, gender, salary) VALUE ('003', 'arraynetworks', 30, 'man', '19000')")
self.connect.commit()
def update(self):
"""
更新数据
:return:
"""
self.cursor.execute("update tb_gjs set phone='13666666666' where name='qiubing';")
self.connect.commit()
def disconnect_db(self):
"""
断开数据库
:return:
"""
self.connect.commit()
self.connect.close()
def alter_table(self):
"""
新增加列
:return:
"""
self.cursor.execute("alter table tb_gjs add column company varchar(20) after salary;")
self.cursor.execute("""insert into tb_gjs (number, name, age, gender, salary, company) values ('006', 'wanghuan', 22, 'nv', '35000', 'Camlot');""")
self.connect.commit()
def delete_procedure(self):
"""
存储过程
:return:
"""
self.cursor.execute("""
CREATE PROCEDURE delete_age(IN p_age INTEGER)
BEGIN
DELETE FROM tb_gjs
WHERE age = p_age;
END$$
""")
self.cursor.execute("call delete_age(22);")
test = Database()
test.create_table()
test.insert_sql()
test.alter_table()
test.delete_procedure()