Python访问MySQL数据库并实现其增删改查功能
概述：
对于访问数据库的操作，我想大家也都有一些了解。不过，因为最近在学习Python，以下就用Python来实现它。其中包括创建数据库和数据表、插入记录、删除记录、修改记录数据、查询数据、删除数据表、删除数据库。还有一点就是我们最好使用一个新定义的类来处理这件事。因为这会使在以后的使用过程中更加的方便（只需要导入即可，避免了重复制造轮子）。
实现功能介绍：
1.封装一个DB类
2.数据库操作：创建数据库和数据表
3.数据库操作：插入记录
4.数据库操作：一次插入多条记录
5.数据库操作：删除记录
6.数据库操作：修改记录数据
7.数据库操作：一次修改多条记录数据
8.数据库操作：查询数据
9.数据库操作：删除数据表
10.数据库操作：删除数据库
数据库类的定义：
heroDB.py
#!/usr/bin/env python
import MySQLdb
DATABASE_NAME = 'hero'
class HeroDB:
# init class and create a database
def __init__(self, name, conn, cur):
self.name = name
self.conn = conn
self.cur = cur
try:
cur.execute('create database if not exists ' + name)
conn.select_db(name)
conn.commit()
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# create a table
def createTable(self, name):
try:
ex = self.cur.execute
if ex('show tables') == 0:
ex('create table ' + name + '(id int, name varchar(20), sex int, age int, info varchar(50))')
self.conn.commit()
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# insert single record
def insert(self, name, value):
try:
self.cur.execute('insert into ' + name + ' values(%s,%s,%s,%s,%s)', value)
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# insert more records
def insertMore(self, name, values):
try:
self.cur.executemany('insert into ' + name + ' values(%s,%s,%s,%s,%s)', values)
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# update single record from table
# name: table name
# values: waiting to update data
def updateSingle(self, name, value):
try:
# self.cur.execute('update ' + name + ' set name=' + str(values[1]) + ', sex=' + str(values[2]) + ', age=' + str(values[3]) + ', info=' + str(values[4]) + ' where id=' + str(values[0]) + ';')
self.cur.execute('update ' + name + ' set name=%s, sex=%s, age=%s, info=%s where id=%s;', value)
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# update some record from table
def update(self, name, values):
try:
self.cur.executemany('update ' + name + ' set name=%s, sex=%s, age=%s, info=%s where id=%s;', values)
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# get record count from db table
def getCount(self, name):
try:
count = self.cur.execute('select * from ' + name)
return count
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# select first record from database
def selectFirst(self, name):
try:
self.cur.execute('select * from ' + name + ';')
result = self.cur.fetchone()
return result
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# select last record from database
def selectLast(self, name):
try:
self.cur.execute('SELECT * FROM ' + name + ' ORDER BY id DESC;')
result = self.cur.fetchone()
return result
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# select next n records from database
def selectNRecord(self, name, n):
try:
self.cur.execute('select * from ' + name + ';')
results = self.cur.fetchmany(n)
return results
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# select all records
def selectAll(self, name):
try:
self.cur.execute('select * from ' + name + ';')
self.cur.scroll(0, mode='absolute') # reset cursor location (mode = absolute | relative)
results = self.cur.fetchall()
return results
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# delete a record
def deleteByID(self, name, id):
try:
self.cur.execute('delete from ' + name + ' where id=%s;', id)
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# delete some record
def deleteSome(self, name):
pass
# drop the table
def dropTable(self, name):
try:
self.cur.execute('drop table ' + name + ';')
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# drop the database
def dropDB(self, name):
try:
self.cur.execute('drop database ' + name + ';')
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def __del__(self):
if self.cur != None:
self.cur.close()
if self.conn != None:
self.conn.close()使用范例：
testHeroDB.py
#!/usr/bin/env python
import MySQLdb
from heroDB import HeroDB
def main():
conn = MySQLdb.connect(host='localhost', user='root', passwd='260606', db='hero', port=3306, charset='utf8')
cur = conn.cursor()
# ------------------------------------------- create -----------------------------------------------------
hero = HeroDB('hero', conn, cur)
hero.createTable('heros')
# ------------------------------------------- insert -----------------------------------------------------
hero.insert('heros', [3, 'Prophet', 0, 2000, 'The hero who in fairy tale.'])
# ------------------------------------------- select -----------------------------------------------------
print '-' * 60
print 'first record'
result = hero.selectFirst('heros')
print result
print '-' * 60
print 'last record'
result = hero.selectLast('heros')
print result
print '-' * 60
print 'more record'
results = hero.selectNRecord('heros', 3)
for item in results:
print item
print '-' * 60
print 'all record'
results = hero.selectAll('heros')
for item in results:
print item
# ------------------------------------------- update -----------------------------------------------------
hero.updateSingle('heros', ['Zeus', 1, 22000, 'The god.', 2])
values = []
values.append(['SunWukong', 1, 1300, 'The hero who in fairy tale.', 1])
values.append(['Zeus', 1, 50000, 'The king who in The Quartet myth.', 2])
values.append(['Prophet', 1, 20000, 'The hero who in fairy tale.3', 3])
hero.update('heros', values)
# ------------------------------------------- delete -----------------------------------------------------
hero.deleteByID('heros', 1)
hero.dropTable('heros')
hero.dropDB('hero')
if __name__ == '__main__':
main()
注：请不要不假思索地使用他们。如果你想实现某一个功能点，请最好将其他的功能点注释掉，这样才符合单元测试的规范。
代码优化：
# encoding:utf-8
#!/usr/bin/env python
import MySQLdb
import dbConfig
class HeroDB:
# init class and create a database
def __init__(self, dbname = None, dbhost = None):
if dbname is None:
self._dbname = dbConfig.DBNAME
else:
self._dbname = dbname
if dbhost is None:
self._dbhost = dbConfig.DBHOST
else:
self._dbhost = dbhost
self._dbuser = dbConfig.DBUSER
self._dbpassword = dbConfig.DBPWD
self._dbport = dbConfig.DBPORT
self._dbcharset = dbConfig.DBCHARSET
self._conn = self.connectMySQL()
if(self._conn):
self._cursor = self._conn.cursor()
# 连接数据库
def connectMySQL(self):
conn = False
try:
conn = MySQLdb.connect(host=self._dbhost,
user=self._dbuser,
passwd=self._dbpassword,
port=self._dbport,
db=self._dbname,
charset=self._dbcharset)
except Exception, data:
print "connect database failed, %s" % data
conn = False
return conn
# 创建数据表
def createTable(self, sql):
flag = False
try:
ex = self._cursor.execute
ex(sql)
self._conn.commit()
flag = True
except MySQLdb.Error, e:
flag = False
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
return flag
# 删除数据表
def dropTable(self, sql):
flag = False
try:
self._cursor.execute(sql)
self._conn.commit()
flag = True
except MySQLdb.Error, e:
flag = False
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
return flag
# 添加一条记录
def insert(self, sql):
flag = False
if(self._conn):
try:
self._cursor.execute(sql)
self._conn.commit()
flag = True
except Exception, data:
flag = False
print "update database exception, %s" % data
return flag
# 获取查询结果集(全部)
def fetch_all(self, sql):
res = ''
if(self._conn):
try:
self._cursor.execute(sql)
res = self._cursor.fetchall()
except Exception, data:
res = False
print "query database exception, %s" % data
return res
# 更新数据库
def update(self, sql):
flag = False
if(self._conn):
try:
self._cursor.execute(sql)
self._conn.commit()
flag = True
except Exception, data:
flag = False
print "update database exception, %s" % data
return flag
# 获得数据表中的记录数
def getCount(self, sql):
count = 0
try:
count = self._cursor.execute(sql)
self._conn.commit()
except MySQLdb.Error, e:
count = 0
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
return count
# 删除记录
def delete(self, sql):
flag = False
if(self._conn):
try:
self._cursor.execute(sql)
self._conn.commit()
flag = True
except Exception, data:
flag = False
print "update database exception, %s" % data
return flag
# 关闭数据库连接
def close(self):
if(self._conn):
try:
if(type(self._cursor)=='object'):
self._cursor.close()
if(type(self._conn)=='object'):
self._conn.close()
except Exception, data:
print "close database exception, %s,%s,%s" % (data, type(self._cursor), type(self._conn))上面的代码做了一些优化，使代码看上去更简洁、灵活以及对后期维护的考虑。
