Python操作mysql數(shù)據(jù)庫實(shí)現(xiàn)增刪查改功能的方法
更新時(shí)間：2018年01月15日 10:34:17 作者：壞蛋是我
這篇文章主要介紹了Python操作mysql數(shù)據(jù)庫實(shí)現(xiàn)增刪查改功能的方法,涉及Python針對(duì)mysql數(shù)據(jù)庫的連接、增刪改查等相關(guān)操作技巧,需要的朋友可以參考下
本文實(shí)例講述了Python操作mysql數(shù)據(jù)庫實(shí)現(xiàn)增刪查改功能的方法。分享給大家供大家參考，具體如下：
#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:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def create_table(self,table):
try:
if table=="dept":
#創(chuàng)建
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,2),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":
#創(chuàng)建
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"輸入錯(cuò)誤的表名，表明為dept、emp、salgrade、stu..."
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], 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,%s,%s,%s,%s,%s,%s)"
param = (1009, 'a niu', 'dong shi zhang', NULL, '2001-11-17', 50000, NULL, 10)
n = cursor.execute(sql,param)
print 'insert',n
elif table=="salgrade":
sql = "insert into salgrade values(%s,%s,%s)"
param = (1, 7000, 12000)
n = cursor.execute(sql,param)
print 'insert',n
elif table=="stu":
sql = "insert into stu values(%s,%s,%s,%s,%s,%s)"
param = ('1', '001', '23', 'nan', 'bei jing', '1500')
n = cursor.execute(sql,param)
print 'insert',n
else:
print u"輸入錯(cuò)誤的表名，表明為dept、emp、salgrade、stu..."
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def inser_muldata_table(self,table):
try:
if table=="dept":
sql = "insert into dept values(%s,%s,%s)"
param = ((10, 'jiao yan bu', 'bei jing'),(20, 'xue gong bu', 'shang hai'),(30, 'xiao shou bu', 'guang zhou'))
n = cursor.executemany(sql,param)
print 'insert',n
elif table=="emp":
sql = "insert into emp values(%s,%s,%s,%s,%s,%s,%s,%s)"
param = ((1004, 'liu bei', 'jing li', 1009, '2001-04-02', 29750, NULL, 20),
(1006, 'guan yu', 'jing li', 1009, '2001-05-01', 28500, NULL, 30),
(1008, 'zhu ge liang', 'fen xi shi', 1004, '2007-04-19', 30000, NULL, 20),
(1013, 'pang', 'fen xi shi', 1004, '2001-12-03', 30000, NULL, 20),
(1002, 'dai', 'xiao shou yuan', 1006, '2001-02-20', 16000, 3000, 30),
(1003, 'tian zheng', 'xiao shou yuan', 1006, '2001-02-22', 12500, 5000, 30),
(1005, 'xie xun', 'xiao shou yuan', 1006, '2001-09-28', 12500, 14000, 30),
(1010, 'wei yi xiao', 'xiao shou yuan', 1006, '2001-09-08', 15000, 0, 30)
)
n = cursor.executemany(sql,param)
print 'insert',n
elif table=="salgrade":
sql = "insert into salgrade values(%s,%s,%s)"
param = ((2, 12010, 14000),(3, 14010, 20000),(4, 20010, 30000),(5, 30010, 99990))
n = cursor.executemany(sql,param)
print 'insert',n
elif table=="stu":
sql = "insert into stu values(%s,%s,%s,%s,%s,%s)"
param = ( ('2', '002', '25', 'nan', 'liao ning', '2500'),
('3', '003', '22', 'nan', 'bei jing', '3500'),
('4', '004', '25', 'nan', 'bei jing', '1500'),
('5', '005', '23', 'nv', 'bei jing', '1000'),
('6', '006', '22', 'nv', 'shan dong', '2500'),
('7', '007', '21', 'nv', 'bei jing', '1600'),
('8', '008', '23', 'nan', 'bei jing', '3500'),
('9', '009', '23', 'nv', 'guang zhou', '2500'),
('10', '010', '18', 'nan', 'shan xi', '3500'),
('11', '011', '23', 'nan', 'hu bei', '4500'),
('12', '011', '24', 'nan', 'bei jing', '1500'),
('13', '011', '24', 'nan', 'liao ning', '2500'),
('14', '011', '22', 'nan', 'bei jing', '3500'),
('15', '011', '25', 'nan', 'bei jing', '1500'),
('16', '011', '23', 'nv', 'bei jing', '1000'),
('17', '011', '22', 'nv', 'shan dong', '2500'),
('18', '011', '21', 'nv', 'bei jing', '1600'),
('19', '011', '23', 'nan', 'bei jing', '3500'),
('20', '011', '23', 'nv', 'guang zhou', '2500'),
('21', '011', '18', 'nan', 'shan xi', '3500'),
('22', '011', '23', 'nan', 'hu bei', '4500'),
('23', '011', '23', 'nan', 'bei jing', '1500'),
('24', '011', '25', 'nan', 'liao ning', '2500'),
('25', '011', '22', 'nan', 'bei jing', '3500'),
('26', '011', '25', 'nan', 'bei jing', '1500'),
('27', '011', '23', 'nv', 'bei jing', '1000'),
('28', '011', '22', 'nv', 'shan dong', '2500'),
('29', '011', '21', 'nv', 'bei jing', '1600'),
('30', '011', '23', 'nan', 'bei jing', '3500'),
('31', '011', '23', 'nv', 'guang zhou', '2500'),
('32', '011', '18', 'nan', 'shan xi', '3500'),
('33', '033', '23', 'nan', 'hu bei', '4500'),
('34', '034', '23', 'nan', 'bei jing', '1500'),
('35', '035', '25', 'nan', 'liao ning', '2500'),
('36', '036', '22', 'nan', 'bei jing', '3500'),
('37', '037', '25', 'nan', 'bei jing', '1500'),
('38', '038', '23', 'nv', 'bei jing', '1000'),
('39', '039', '22', 'nv', 'shan dong', '2500'),
('40', '040', '21', 'nv', 'bei jing', '1600'),
('41', '041', '23', 'nan', 'bei jing', '3500'),
('42', '042', '23', 'nv', 'guang zhou', '2500'),
('43', '043', '18', 'nan', 'shan xi', '3500'),
('44', '044', '23', 'nan', 'hu bei', '4500'),
('45', '045', '23', 'nan', 'bei jing', '1500'),
('46', '046', '25', 'nan', 'liao ning', '2500'),
('47', '047', '22', 'nan', 'bei jing', '3500'),
('48', '048', '25', 'nan', 'bei jing', '1500'),
('49', '049', '23', 'nv', 'bei jing', '1000'),
('50', '050', '22', 'nv', 'shan dong', '2500'),
('51', '051', '21', 'nv', 'bei jing', '1600'),
('52', '052', '23', 'nan', 'bei jing', '3500'),
('53', '053', '23', 'nv', 'guang zhou', '2500'),
('54', '054', '18', 'nan', 'shan xi', '3500'),
('55', '055', '23', 'nan', 'hu bei', '4500')
)
n = cursor.executemany(sql,param)
print 'insert',n
else:
print u"輸入錯(cuò)誤的表名，表明為dept、emp、salgrade、stu..."
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def update_table(self,table,no,upno):
try:
if table=="dept":
#創(chuàng)建
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,param)
print 'update',n
elif table=="salgrade":
sql = "update salgrade set grade=%s where grade=" +no
param = (upno)
n = cursor.execute(sql,param)
print 'update',n
elif table=="stu":
sql = "update stu set sname=%s where sname=" +no
param = (upno)
n = cursor.execute(sql,param)
print 'update',n
else:
print u"輸入錯(cuò)誤的表名，表明為dept、emp、salgrade、stu..."
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], 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:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def delete_data(self,table,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,param)
print 'delete',n
elif table=="salgrade":
sql = "delete from salgrade where grade=%s"
param = (upno)
n = cursor.execute(sql,param)
print 'delete',n
elif table=="stu":
sql = "delete from stu where sname=%s "
param = (upno)
n = cursor.execute(sql,param)
print 'delete',n
else:
print u"輸入錯(cuò)誤的表名，表明為dept、emp、salgrade、stu..."
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
del down_db(self):
try:
cursor.close()
#提交
conn.commit()
#關(guān)閉
conn.close()
except MySQLdb.Error,e:
print "Mysql Error %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")
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()
后期我會(huì)把數(shù)據(jù)整合到CSV文件中，操作CSV文件對(duì)數(shù)據(jù)進(jìn)行操作
更多關(guān)于Python相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《Python+MySQL數(shù)據(jù)庫程序設(shè)計(jì)入門教程》、《Python常見數(shù)據(jù)庫操作技巧匯總》、《Python數(shù)學(xué)運(yùn)算技巧總結(jié)》、《Python數(shù)據(jù)結(jié)構(gòu)與算法教程》、《Python函數(shù)使用技巧總結(jié)》、《Python字符串操作技巧匯總》、《Python入門與進(jìn)階經(jīng)典教程》及《Python文件與目錄操作技巧匯總》
希望本文所述對(duì)大家Python程序設(shè)計(jì)有所幫助。
相關(guān)文章
測(cè)試系統(tǒng)環(huán)境 Windows 2003 python 2.5.1 mysql 5.0.1 應(yīng)該只適用于Win，因?yàn)檎{(diào)用了CMD。 增量備份，因?yàn)樽杂?，?shù)據(jù)庫不大。2008-07-07
Anaconda中利用conda創(chuàng)建、激活、刪除、添加新環(huán)境
在使用Python開發(fā)項(xiàng)目或者編寫腳本的時(shí)候通常需要建立不同版本的Python的虛擬環(huán)境,本文主要介紹了Anaconda中利用conda創(chuàng)建、激活、刪除、添加新環(huán)境,具有一定的參考價(jià)值,感興趣的可以了解一下2024-04-04
python實(shí)現(xiàn)數(shù)據(jù)挖掘中分箱的示例代碼
數(shù)據(jù)分箱（英語：Data?binning）是一種數(shù)據(jù)預(yù)處理方法,用于最大限度地減少小觀測(cè)誤差的影響,本文主要為大家介紹了python實(shí)現(xiàn)數(shù)據(jù)分箱的相關(guān)知識(shí),感興趣的可以了解下2024-01-01
python3實(shí)現(xiàn)mysql導(dǎo)出excel的方法
這篇文章主要介紹了python3實(shí)現(xiàn)mysql導(dǎo)出excel的方法，本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值 ,需要的朋友可以參考下2019-07-07
TensorFlow實(shí)現(xiàn)簡(jiǎn)單的CNN的方法
這篇文章主要介紹了TensorFlow實(shí)現(xiàn)簡(jiǎn)單的CNN的方法，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-07-07
對(duì)python中矩陣相加函數(shù)sum()的使用詳解
今天小編就為大家分享一篇對(duì)python中矩陣相加函數(shù)sum()的使用詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2019-01-01
np.newaxis常常用于將一個(gè)一維數(shù)組轉(zhuǎn)化為二維數(shù)組,本文就來介紹一下Numpy中np.newaxis的作用和用法小結(jié),具有一定的參考價(jià)值,感興趣的可以了解一下2024-03-03
Python實(shí)現(xiàn)的txt文件去重功能示例
這篇文章主要介紹了Python實(shí)現(xiàn)的txt文件去重功能,涉及Python針對(duì)txt文本文件的讀寫、字符串遍歷、判斷相關(guān)操作技巧,需要的朋友可以參考下2018-07-07
這篇文章主要介紹了Python分析最近大火的網(wǎng)劇《隱秘的角落》,本文通過實(shí)例代碼圖文相結(jié)合給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-07-07
在 Python 的項(xiàng)目中，如何管理所用的全部依賴庫呢？最主流的做法是維護(hù)一份“requirements.txt”，記錄下依賴庫的名字及其版本號(hào)，需要的朋友可以參考下2019-11-11