python串連mysql資料庫樣本(做增刪改操作)

一、相關代碼
資料庫配置類 MysqlDBConn.py

代碼如下:

#encoding=utf-8
'''
Created on 2012-11-12

Mysql Conn串連類
'''

import MySQLdb

class DBConn:

conn = None

#建立和資料庫系統的串連
def connect(self):
self.conn = MySQLdb.connect(host="localhost",port=3306,user="house", passwd="house" ,db="house",charset="utf8")

#擷取操作遊標
def cursor(self):
try:
return self.conn.cursor()
except (AttributeError, MySQLdb.OperationalError):
self.connect()
return self.conn.cursor()

def commit(self):
return self.conn.commit()

#關閉串連
def close(self):
return self.conn.close()

MysqlDemo.py類

代碼如下:

#encoding=utf-8
'''
Created on 2012-11-12

@author: Steven

Mysql操作Demo
Done:建立表，刪除表，資料增、刪、改，批量插入
'''
import MysqlDBConn

dbconn = MysqlDBConn.DBConn()

def process():
#建立串連
dbconn.connect()
#刪除表
dropTable()
#建立表
createTable()
#批量插入資料
insertDatas()
#單條插入
insertData()
#更新資料
updateData()
#刪除資料
deleteData()
#查詢資料
queryData()
#釋放串連
dbconn.close()

def insertDatas():
sql = "insert into lifeba_users(name, realname, age) values(%s, %s, %s)"
tmp = (('steven1', '測試1',26), ('steven2', '測試2',25))
executemany(sql, tmp)

def updateData():
sql = "update lifeba_users set realname = '%s' where name ='steven1'"%("測試1修改")
execute(sql)

def deleteData():
sql = "delete from lifeba_users where id=2"
execute(sql)

def queryData():
sql = "select * from lifeba_users"
rows = query(sql)
printResult(rows)

def insertData():
sql = "insert into lifeba_users(name, realname, age) values('%s', '%s', %s)"%("steven3","測試3","26")
print sql
execute(sql)

def executemany(sql, tmp):
'''插入多條資料'''
conn=dbconn.cursor()
conn.executemany(sql, tmp)

def execute(sql):
'''執行sql'''
conn=dbconn.cursor()
conn.execute(sql)

def query(sql):
'''查詢sql'''
conn=dbconn.cursor()
conn.execute(sql)
rows = conn.fetchmany(10)
return rows

def createTable():
'''建立表'''
conn=dbconn.cursor()
conn.execute('''
CREATE TABLE `lifeba_users` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`realName` varchar(50) default NULL,
`age` int(11) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
''')
# dbconn.commit()

def dropTable():
'''刪除表'''
conn=dbconn.cursor()
conn.execute('''
DROP TABLE IF EXISTS `lifeba_users`
''')
# dbconn.commit()

def printResult(rows):
for row in rows:
for i in range(0,len(row)):#遍曆數組
print row[i], #加, 不換行列印
print ''

if __name__ == '__main__':

process()

