import mysql.connector
class database:
def __init__(self,ip,port,user,psw,dbname):
try:
con=mysql.connector.connect(
host=ip,
user=user,
password=psw,
port=port,
database=dbname,
charset='utf8',
buffered=True
)
print('數(shù)據(jù)庫連接成功')
self.con=con #con在其他類方法中還要多次調(diào)用，所以定義為成員變量
#cursor=con.cursor()
except mysql.connector.Error as e:
print('連接失敗',str(e))
def create_tb(self,sql):
try:
cursor=self.con.cursor()#獲取游標
cursor.execute(sql)#執(zhí)行sql
print('創(chuàng)建成功')
except mysql.connector.Error as e:
print('創(chuàng)建失敗',str(e))
finally:
cursor.close()#關(guān)閉游標
def insert_tb(self,sql,data):
try:
cursor=self.con.cursor()
cursor.executemany(sql,data)
self.con.commit()
print('數(shù)據(jù)插入成功')
except mysql.connector.Error as e:
self.con.rollback()
print('插入失敗',str(e))
cursor.close()
def select_tb(self,sql):
try:
cursor=self.con.cursor(dictionary=True)
cursor.execute(sql)
result1=cursor.fetchall()
print('查詢?nèi)拷Y(jié)果：',result1)
except mysql.connector.Error as e:
print('查詢失敗',str(e))
finally:
cursor.close()
def select_tb_one(self,sql):
try:
cursor=self.con.cursor(dictionary=True)
cursor.execute(sql)
result2=cursor.fetchone()
print('查詢一條結(jié)果：',result2)
except mysql.connector.Error as e:
print('查詢失敗',str(e))
finally:
cursor.close()
def select_tb_many(self,sql,count):
try:
cursor=self.con.cursor(dictionary=True)
cursor.execute(sql)
result3=cursor.fetchmany(count)
print('查詢結(jié)果：',result3)
except mysql.connector.Error as e:
print('查詢失敗',str(e))
finally:
cursor.close()
#連接數(shù)據(jù)庫
db=database('127.0.0.1','3306','root','vertrigo','mysql')
#創(chuàng)建表
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;'
db.create_tb(sql_create)
# #插入數(shù)據(jù)
sql_insert='insert into student_5(id,name,age) values(%s,%s,%s)'
data_insert=[(1,'guozhen',18),(2,'ss',19),(3,'alen',30)]
db.insert_tb(sql_insert,data_insert)
#查詢?nèi)繑?shù)據(jù)
sql_select='select * from student_5'
db.select_tb(sql_select)
#查詢多條數(shù)據(jù)
db.select_tb_many(sql_select,2)
#查詢一條數(shù)據(jù)
db.select_tb_one(sql_select)
#關(guān)閉數(shù)據(jù)庫連接
db.con.close()
