Python操作mysql数据库实现增删查改功能的方法
本文实例讲述了Python操作mysql数据库实现增删查改功能的方法。分享给大家供大家参考，具体如下：
#coding=utf-8
importMySQLdb
classMysql_Oper:
def__init__(self,host,user,passwd,db):
self.host=host
self.user=user
self.passwd=passwd
self.database=db
defdb_connecet(self):
try:
#连接
conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.database,charset="utf8")
cursor=conn.cursor()
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
defdrop_table(self,table):
try:
#删除表
sql="droptableifexists"+table
cursor.execute(sql)
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
defcreate_table(self,table):
try:
iftable=="dept":
#创建
sql="createtableifnotexistsdept(deptnointprimarykey,dnamevarchar(50),locvarchar(50))"
cursor.execute(sql)
eliftable=="emp":
sql=="createtableifnotexistsemp(empnoINTPRIMARYKEY,enameVARCHAR(50),jobVARCHAR(50),mgrINT,hiredateDATE,salDECIMAL(7,2),COMMDECIMAL(7,2),deptnoINT,locvarchar(50),CONSTRAINTfk_empFOREIGNKEY(mgr)REFERENCESemp(empno))"
cursor.execute(sql)
eliftable=="salgrade":
sql="createtableifnotexistssalgrade(gradeINTPRIMARYKEY,losalINT,hisalINT)"
cursor.execute(sql)
eliftable=="stu":
#创建
sql="createtableifnotexistsdept(sidINTPRIMARYKEY,snameVARCHAR(50),ageINT,ganderVARCHAR(10),provinceVARCHAR(50),tuitionINT)"
cursor.execute(sql)
else:
printu"输入错误的表名，表明为dept、emp、salgrade、stu..."
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
definser_onedata_table(self,table):
try:
iftable=="dept":
sql="insertintodeptvalues(%s,%s,%s)"
param=(40,'caiwubu','wuhan')
n=cursor.execute(sql,param)
print'insert',n
eliftable=="emp":
sql="insertintoempvalues(%s,%s,%s,%s,%s,%s,%s,%s)"
param=(1009,'aniu','dongshizhang',NULL,'2001-11-17',50000,NULL,10)
n=cursor.execute(sql,param)
print'insert',n
eliftable=="salgrade":
sql="insertintosalgradevalues(%s,%s,%s)"
param=(1,7000,12000)
n=cursor.execute(sql,param)
print'insert',n
eliftable=="stu":
sql="insertintostuvalues(%s,%s,%s,%s,%s,%s)"
param=('1','001','23','nan','beijing','1500')
n=cursor.execute(sql,param)
print'insert',n
else:
printu"输入错误的表名，表明为dept、emp、salgrade、stu..."
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
definser_muldata_table(self,table):
try:
iftable=="dept":
sql="insertintodeptvalues(%s,%s,%s)"
param=((10,'jiaoyanbu','beijing'),(20,'xuegongbu','shanghai'),(30,'xiaoshoubu','guangzhou'))
n=cursor.executemany(sql,param)
print'insert',n
eliftable=="emp":
sql="insertintoempvalues(%s,%s,%s,%s,%s,%s,%s,%s)"
param=((1004,'liubei','jingli',1009,'2001-04-02',29750,NULL,20),
(1006,'guanyu','jingli',1009,'2001-05-01',28500,NULL,30),
(1008,'zhugeliang','fenxishi',1004,'2007-04-19',30000,NULL,20),
(1013,'pang','fenxishi',1004,'2001-12-03',30000,NULL,20),
(1002,'dai','xiaoshouyuan',1006,'2001-02-20',16000,3000,30),
(1003,'tianzheng','xiaoshouyuan',1006,'2001-02-22',12500,5000,30),
(1005,'xiexun','xiaoshouyuan',1006,'2001-09-28',12500,14000,30),
(1010,'weiyixiao','xiaoshouyuan',1006,'2001-09-08',15000,0,30)
)
n=cursor.executemany(sql,param)
print'insert',n
eliftable=="salgrade":
sql="insertintosalgradevalues(%s,%s,%s)"
param=((2,12010,14000),(3,14010,20000),(4,20010,30000),(5,30010,99990))
n=cursor.executemany(sql,param)
print'insert',n
eliftable=="stu":
sql="insertintostuvalues(%s,%s,%s,%s,%s,%s)"
param=(('2','002','25','nan','liaoning','2500'),
('3','003','22','nan','beijing','3500'),
('4','004','25','nan','beijing','1500'),
('5','005','23','nv','beijing','1000'),
('6','006','22','nv','shandong','2500'),
('7','007','21','nv','beijing','1600'),
('8','008','23','nan','beijing','3500'),
('9','009','23','nv','guangzhou','2500'),
('10','010','18','nan','shanxi','3500'),
('11','011','23','nan','hubei','4500'),
('12','011','24','nan','beijing','1500'),
('13','011','24','nan','liaoning','2500'),
('14','011','22','nan','beijing','3500'),
('15','011','25','nan','beijing','1500'),
('16','011','23','nv','beijing','1000'),
('17','011','22','nv','shandong','2500'),
('18','011','21','nv','beijing','1600'),
('19','011','23','nan','beijing','3500'),
('20','011','23','nv','guangzhou','2500'),
('21','011','18','nan','shanxi','3500'),
('22','011','23','nan','hubei','4500'),
('23','011','23','nan','beijing','1500'),
('24','011','25','nan','liaoning','2500'),
('25','011','22','nan','beijing','3500'),
('26','011','25','nan','beijing','1500'),
('27','011','23','nv','beijing','1000'),
('28','011','22','nv','shandong','2500'),
('29','011','21','nv','beijing','1600'),
('30','011','23','nan','beijing','3500'),
('31','011','23','nv','guangzhou','2500'),
('32','011','18','nan','shanxi','3500'),
('33','033','23','nan','hubei','4500'),
('34','034','23','nan','beijing','1500'),
('35','035','25','nan','liaoning','2500'),
('36','036','22','nan','beijing','3500'),
('37','037','25','nan','beijing','1500'),
('38','038','23','nv','beijing','1000'),
('39','039','22','nv','shandong','2500'),
('40','040','21','nv','beijing','1600'),
('41','041','23','nan','beijing','3500'),
('42','042','23','nv','guangzhou','2500'),
('43','043','18','nan','shanxi','3500'),
('44','044','23','nan','hubei','4500'),
('45','045','23','nan','beijing','1500'),
('46','046','25','nan','liaoning','2500'),
('47','047','22','nan','beijing','3500'),
('48','048','25','nan','beijing','1500'),
('49','049','23','nv','beijing','1000'),
('50','050','22','nv','shandong','2500'),
('51','051','21','nv','beijing','1600'),
('52','052','23','nan','beijing','3500'),
('53','053','23','nv','guangzhou','2500'),
('54','054','18','nan','shanxi','3500'),
('55','055','23','nan','hubei','4500')
)
n=cursor.executemany(sql,param)
print'insert',n
else:
printu"输入错误的表名，表明为dept、emp、salgrade、stu..."
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
defupdate_table(self,table,no,upno):
try:
iftable=="dept":
#创建
sql="updatedeptsetdeptno=%swheredeptno="+no
param=(upno)
n=cursor.execute(sql,param)
print'update',n
eliftable=="emp":
sql="updateempsetempno=%swhereempno="+no
param=(upno)
n=cursor.execute(sql,param)
print'update',n
eliftable=="salgrade":
sql="updatesalgradesetgrade=%swheregrade="+no
param=(upno)
n=cursor.execute(sql,param)
print'update',n
eliftable=="stu":
sql="updatestusetsname=%swheresname="+no
param=(upno)
n=cursor.execute(sql,param)
print'update',n
else:
printu"输入错误的表名，表明为dept、emp、salgrade、stu..."
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
defquery_data(self,table):
try:
#查询
sql="select*from"+table
n=cursor.execute(sql)
printcursor.fetchall()
forrowincursor.fetchall():
printrow
forrinrow:
printr
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
defdelete_data(self,table,no)
try:
iftable=="dept":
sql="deletefromdeptwheredeptno=%s"
param=(upno)
n=cursor.execute(sql,param)
print'delete',n
eliftable=="emp":
sql="deletefromempwhereempno=%s"
param=(upno)
n=cursor.execute(sql,param)
print'delete',n
eliftable=="salgrade":
sql="deletefromsalgradewheregrade=%s"
param=(upno)
n=cursor.execute(sql,param)
print'delete',n
eliftable=="stu":
sql="deletefromstuwheresname=%s"
param=(upno)
n=cursor.execute(sql,param)
print'delete',n
else:
printu"输入错误的表名，表明为dept、emp、salgrade、stu..."
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
deldown_db(self):
try:
cursor.close()
#提交
conn.commit()
#关闭
conn.close()
exceptMySQLdb.Error,e:
print"MysqlError%d:%s"%(e.args[0],e.args[1])
mysqlDB=Mysql_Oper("127.0.0.1","root","root","exam")
mysqlDB.db_connecet()
mysqlDB.drop_table("dept")
fortablein["dept","emp","salgrade","stu"]
mysqlDB.create_table(table)
mysqlDB.inser_onedata_table(table)
mysqlDB.inser_muldata_table(table)
mysqlDB.query_data(table)
mysqlDB.down_db()
后期我会把数据整合到CSV文件中，操作CSV文件对数据进行操作
更多关于Python相关内容感兴趣的读者可查看本站专题：《Python+MySQL数据库程序设计入门教程》、《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》
希望本文所述对大家Python程序设计有所帮助。