Python操作mysql数据库实现增删查改功能的方法
发布时间：2020-12-17 07:32:46 所属栏目：Python 来源：网络整理
导读：本篇章节讲解Python操作mysql数据库实现增删查改功能的方法。供大家参考研究具体如下： #coding=utf-8import MySQLdbclass Mysql_Oper: def __init__(self,host,user,passwd,db): self.host=host self.user=user self.passwd=passwd self.database
|
本篇章节讲解Python操作mysql数据库实现增删查改功能的方法。分享给大家供大家参考，具体如下：
#coding=utf-8
import MySQLdb
class Mysql_Oper:
def __init__(self,host,user,passwd,db):
self.host=host
self.user=user
self.passwd=passwd
self.database=db
def db_connecet(self):
try:
#连接
conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.database,charset="utf8")
cursor = conn.cursor()
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0],e.args[1])
def drop_table(self,table):
try:
#删除表
sql = "drop table if exists" + table
cursor.execute(sql)
except MySQLdb.Error,e.args[1])
def create_table(self,table):
try:
if table=="dept":
#创建
sql = "create table if not exists dept(deptno int primary key,dname varchar(50),loc varchar(50))"
cursor.execute(sql)
elif table=="emp":
sql == "create table if not exists emp(empno INT PRIMARY KEY,ename VARCHAR(50),job VARCHAR(50),mgr INT,hiredate DATE,sal DECIMAL(7,2),COMM DECIMAL(7,deptno INT,loc varchar(50),CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno))"
cursor.execute(sql)
elif table=="salgrade":
sql = "create table if not exists salgrade(grade INT PRIMARY KEY,losal INT,hisal INT)"
cursor.execute(sql)
elif table=="stu":
#创建
sql = "create table if not exists dept(sid INT PRIMARY KEY,sname VARCHAR(50),age INT,gander VARCHAR(10),province VARCHAR(50),tuition INT)"
cursor.execute(sql)
else:
print u"输入错误的表名，表明为dept、emp、salgrade、stu..."
except MySQLdb.Error,e.args[1])
def inser_onedata_table(self,table):
try:
if table=="dept":
sql = "insert into dept values(%s,%s,%s)"
param = (40,'cai wu bu','wu han')
n = cursor.execute(sql,param)
print 'insert',n
elif table=="emp":
sql = "insert into emp values(%s,%s)"
param = (1009,'a niu','dong shi zhang',NULL,'2001-11-17',50000,10)
n = cursor.execute(sql,n
elif table=="salgrade":
sql = "insert into salgrade values(%s,%s)"
param = (1,7000,12000)
n = cursor.execute(sql,n
elif table=="stu":
sql = "insert into stu values(%s,%s)"
param = ('1','001','23','nan','bei jing','1500')
n = cursor.execute(sql,n
else:
print u"输入错误的表名，表明为dept、emp、salgrade、stu..."
except MySQLdb.Error,e.args[1])
def inser_muldata_table(self,%s)"
param = ((10,'jiao yan bu','bei jing'),(20,'xue gong bu','shang hai'),(30,'xiao shou bu','guang zhou'))
n = cursor.executemany(sql,%s)"
param = ((1004,'liu bei','jing li',1009,'2001-04-02',29750,20),(1006,'guan yu','2001-05-01',28500,30),(1008,'zhu ge liang','fen xi shi',1004,'2007-04-19',30000,(1013,'pang','2001-12-03',(1002,'dai','xiao shou yuan',1006,'2001-02-20',16000,3000,(1003,'tian zheng','2001-02-22',12500,5000,(1005,'xie xun','2001-09-28',14000,(1010,'wei yi xiao','2001-09-08',15000,30)
)
n = cursor.executemany(sql,%s)"
param = ((2,12010,14000),(3,14010,20000),(4,20010,30000),(5,30010,99990))
n = cursor.executemany(sql,%s)"
param = ( ('2','002','25','liao ning','2500'),('3','003','22','3500'),('4','004','1500'),('5','005','nv','1000'),('6','006','shan dong',('7','007','21','1600'),('8','008',('9','009','guang zhou',('10','010','18','shan xi',('11','011','hu bei','4500'),('12','24',('13',('14',('15',('16',('17',('18',('19',('20',('21',('22',('23',('24',('25',('26',('27',('28',('29',('30',('31',('32',('33','033',('34','034',('35','035',('36','036',('37','037',('38','038',('39','039',('40','040',('41','041',('42','042',('43','043',('44','044',('45','045',('46','046',('47','047',('48','048',('49','049',('50','050',('51','051',('52','052',('53','053',('54','054',('55','055','4500')
)
n = cursor.executemany(sql,e.args[1])
def update_table(self,table,no,upno):
try:
if table=="dept":
#创建
sql = "update dept set deptno=%s where deptno=" +no
param = (upno)
n = cursor.execute(sql,param)
print 'update',n
elif table=="emp":
sql = "update emp set empno=%s where empno=" +no
param = (upno)
n = cursor.execute(sql,n
elif table=="salgrade":
sql = "update salgrade set grade=%s where grade=" +no
param = (upno)
n = cursor.execute(sql,n
elif table=="stu":
sql = "update stu set sname=%s where sname=" +no
param = (upno)
n = cursor.execute(sql,e.args[1])
def query_data(self,table):
try:
#查询
sql="select * from "+table
n = cursor.execute(sql)
print cursor.fetchall()
for row in cursor.fetchall():
print row
for r in row:
print r
except MySQLdb.Error,e.args[1])
def delete_data(self,no)
try:
if table=="dept":
sql = "delete from dept where deptno=%s"
param = (upno)
n = cursor.execute(sql,param)
print 'delete',n
elif table=="emp":
sql = "delete from emp where empno=%s"
param = (upno)
n = cursor.execute(sql,n
elif table=="salgrade":
sql = "delete from salgrade where grade=%s"
param = (upno)
n = cursor.execute(sql,n
elif table=="stu":
sql = "delete from stu where sname=%s "
param = (upno)
n = cursor.execute(sql,e.args[1])
del down_db(self):
try:
cursor.close()
#提交
conn.commit()
#关闭
conn.close()
except MySQLdb.Error,e.args[1])
mysqlDB=Mysql_Oper("127.0.0.1","root","exam")
mysqlDB.db_connecet()
mysqlDB.drop_table("dept")
for table in ["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程序设计有所帮助。 您可能感兴趣的文章:
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |