Pytho操作MySQL
一、相关代码
数据库配置类 MysqlDBConn.py
01 |
#encoding=utf-8 |
02 |
''' |
03 |
Created on 2012-11-12 |
04 |
|
05 |
@author: Steven http://www.lifeba.org |
06 |
|
07 |
Mysql Conn连接类 |
08 |
''' |
09 |
|
10 |
import MySQLdb |
11 |
|
12 |
class DBConn: |
13 |
|
14 |
conn = None |
15 |
|
16 |
#建立和数据库系统的连接 |
17 |
def connect(self): |
18 |
self.conn = MySQLdb.connect(host="localhost",port=3306,user="house", passwd="house" ,db="house",charset="utf8") |
19 |
|
20 |
#获取操作游标 |
21 |
def cursor(self): |
22 |
try: |
23 |
return self.conn.cursor() |
24 |
except (AttributeError, MySQLdb.OperationalError): |
25 |
self.connect() |
26 |
return self.conn.cursor() |
27 |
|
28 |
def commit(self): |
29 |
return self.conn.commit() |
30 |
|
31 |
#关闭连接 |
32 |
def close(self): |
33 |
return self.conn.close() |
MysqlDemo.py类
001 |
#encoding=utf-8 |
002 |
''' |
003 |
Created on 2012-11-12 |
004 |
|
005 |
@author: Steven |
006 |
|
007 |
http://www.lifeba.org |
008 |
|
009 |
Mysql操作Demo |
010 |
Done:创建表，删除表，数据增、删、改，批量插入 |
011 |
''' |
012 |
import MysqlDBConn |
013 |
|
014 |
dbconn = MysqlDBConn.DBConn() |
015 |
|
016 |
def process(): |
017 |
#建立连接 |
018 |
dbconn.connect() |
019 |
#删除表 |
020 |
dropTable() |
021 |
#创建表 |
022 |
createTable() |
023 |
#批量插入数据 |
024 |
insertDatas() |
025 |
#单条插入 |
026 |
insertData() |
027 |
#更新数据 |
028 |
updateData() |
029 |
#删除数据 |
030 |
deleteData() |
031 |
#查询数据 |
032 |
queryData() |
033 |
#释放连接 |
034 |
dbconn.close() |
035 |
|
036 |
def insertDatas(): |
037 |
sql = "insert into lifeba_users(name, realname, age) values(%s, %s, %s)" |
038 |
tmp = (('steven1', '测试1',26), ('steven2', '测试2',25)) |
039 |
executemany(sql, tmp) |
040 |
|
041 |
def updateData(): |
042 |
sql = "update lifeba_users set realname = '%s' where name ='steven1'"%("测试1修改") |
043 |
execute(sql) |
044 |
|
045 |
def deleteData(): |
046 |
sql = "delete from lifeba_users where id=2" |
047 |
execute(sql) |
048 |
|
049 |
def queryData(): |
050 |
sql = "select * from lifeba_users" |
051 |
rows = query(sql) |
052 |
printResult(rows) |
053 |
|
054 |
def insertData(): |
055 |
sql = "insert into lifeba_users(name, realname, age) values('%s', '%s', %s)"%("steven3","测试3","26") |
056 |
print sql |
057 |
execute(sql) |
058 |
|
059 |
def executemany(sql, tmp): |
060 |
'''插入多条数据''' |
061 |
conn=dbconn.cursor() |
062 |
conn.executemany(sql, tmp) |
063 |
|
064 |
def execute(sql): |
065 |
'''执行sql''' |
066 |
conn=dbconn.cursor() |
067 |
conn.execute(sql) |
068 |
|
069 |
def query(sql): |
070 |
'''查询sql''' |
071 |
conn=dbconn.cursor() |
072 |
conn.execute(sql) |
073 |
rows = conn.fetchmany(10) |
074 |
return rows |
075 |
|
076 |
def createTable(): |
077 |
'''创建表''' |
078 |
conn=dbconn.cursor() |
079 |
conn.execute(''' |
080 |
CREATE TABLE `lifeba_users` ( |
081 |
`ID` int(11) NOT NULL auto_increment, |
082 |
`name` varchar(50) default NULL, |
083 |
`realName` varchar(50) default NULL, |
084 |
`age` int(11) default NULL, |
085 |
PRIMARY KEY (`ID`) |
086 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
087 |
''') |
088 |
# dbconn.commit() |
089 |
|
090 |
def dropTable(): |
091 |
'''删除表''' |
092 |
conn=dbconn.cursor() |
093 |
conn.execute(''' |
094 |
DROP TABLE IF EXISTS `lifeba_users` |
095 |
''') |
096 |
# dbconn.commit() |
097 |
|
098 |
def printResult(rows): |
099 |
for row in rows: |
100 |
for i in range(0,len(row)):#遍历数组 |
101 |
print row[i], #加, 不换行打印 |
102 |
print '' |
103 |
|
104 |
if __name__ == '__main__': |
105 |
|
106 |
出自：http://blog.snsgou.com/ |
