1 import mysql.connector 2 class database: 3 def __init__(self,ip,port,user,psw,dbname): 4 5 try: 6 con=mysql.connector.connect( 7 host=ip, 8 user=user, 9 password=psw, 10 port=port, 11 database=dbname, 12 charset='utf8', 13 buffered=True 14 ) 15 print('数据库连接成功') 16 self.con=con #con在其他类方法中还要多次调用，所以定义为成员变量 17 #cursor=con.cursor() 18 except mysql.connector.Error as e: 19 print('连接失败',str(e)) 20 21 def create_tb(self,sql): 22 try: 23 cursor=self.con.cursor()#获取游标 24 cursor.execute(sql)#执行sql 25 print('创建成功') 26 except mysql.connector.Error as e: 27 print('创建失败',str(e)) 28 finally: 29 cursor.close()#关闭游标 30 31 def insert_tb(self,sql,data): 32 try: 33 cursor=self.con.cursor() 34 cursor.executemany(sql,data) 35 self.con.commit() 36 print('数据插入成功') 37 except mysql.connector.Error as e: 38 self.con.rollback() 39 print('插入失败',str(e)) 40 cursor.close() 41 42 def select_tb(self,sql): 43 try: 44 cursor=self.con.cursor(dictionary=True) 45 cursor.execute(sql) 46 result1=cursor.fetchall() 47 print('查询全部结果：',result1) 48 except mysql.connector.Error as e: 49 print('查询失败',str(e)) 50 finally: 51 cursor.close() 52 53 def select_tb_one(self,sql): 54 try: 55 cursor=self.con.cursor(dictionary=True) 56 cursor.execute(sql) 57 result2=cursor.fetchone() 58 print('查询一条结果：',result2) 59 except mysql.connector.Error as e: 60 print('查询失败',str(e)) 61 finally: 62 cursor.close() 63 64 def select_tb_many(self,sql,count): 65 try: 66 cursor=self.con.cursor(dictionary=True) 67 cursor.execute(sql) 68 result3=cursor.fetchmany(count) 69 print('查询结果：',result3) 70 except mysql.connector.Error as e: 71 print('查询失败',str(e)) 72 finally: 73 cursor.close()
调用代码：
1 #连接数据库 2 db=database('127.0.0.1','3306','root','vertrigo','mysql') 3 #创建表 4 sql_create='create table student_5(id int(10) not null auto_increment, name varchar(10) default null, age int(3) default null, primary key (id))engine=myisam default charset=utf8;' 5 db.create_tb(sql_create) 6 # #插入数据 7 sql_insert='insert into student_5(id,name,age) values(%s,%s,%s)' 8 data_insert=[(1,'guozhen',18),(2,'ss',19),(3,'alen',30)] 9 db.insert_tb(sql_insert,data_insert) 10 #查询全部数据 11 sql_select='select * from student_5' 12 db.select_tb(sql_select) 13 #查询多条数据 14 db.select_tb_many(sql_select,2) 15 #查询一条数据 16 db.select_tb_one(sql_select) 17 #关闭数据库连接 18 db.con.close()