python 使用 mysqldb类库操作数据库

1.安装MySQLdb
pip install MySQLdb

2.代码

1. import MySQLdb
   
2. 
   
3. class MysqlSearch(object):
   
4. 
   
5.     def __init__(self):
   
6.         self.get_conn()
   
7. 
   
8.     def get_conn(self):
   
9.         try:
   
10.             self.conn = MySQLdb.connect(
    
11.                 host='127.0.0.1',
    
12.                 user='root',
    
13.                 passwd='root',
    
14.                 db='test',
    
15.                 port=3308,
    
16.                 charset='utf8'
    
17.             )
    
18.         except MySQLdb.Error as e:
    
19.             print('Error: %s' % e)
    
20. 
    
21.     def close_conn(self):
    
22.         try:
    
23.             if self.conn:
    
24.                 # 关闭链接
    
25.                 self.conn.close()
    
26.         except MySQLdb.Error as e:
    
27.             print('Error: %s' % e)
    
28. 
    
29.     def get_one(self):
    
30.         # 准备SQL
    
31.         sql = 'SELECT * FROM `news` WHERE `types` = %s ORDER BY `created_at` DESC;'
    
32.         # 找到cursor
    
33.         cursor = self.conn.cursor()
    
34.         # 执行SQL
    
35.         cursor.execute(sql, ('百家', ))
    
36.         # print(dir(cursor))
    
37.         # 拿到结果
    
38.         rest = dict(zip([k[0] for k in cursor.description], cursor.fetchone()))
    
39.         # 处理数据
    
40.         # 关闭cursor/链接
    
41.         cursor.close()
    
42.         self.close_conn()
    
43.         return rest
    
44. 
    
45.     def get_more(self):
    
46.         # 准备SQL
    
47.         sql = 'SELECT * FROM `news` WHERE `types` = %s ORDER BY `created_at` DESC;'
    
48.         # 找到cursor
    
49.         cursor = self.conn.cursor()
    
50.         # 执行SQL
    
51.         cursor.execute(sql, ('百家', ))
    
52.         # print(dir(cursor))
    
53.         # 拿到结果
    
54.         rest = [dict(zip([k[0] for k in cursor.description], row))
    
55.             for row in cursor.fetchall()]
    
56.         # 处理数据
    
57.         # 关闭cursor/链接
    
58.         cursor.close()
    
59.         self.close_conn()
    
60.         return rest
    
61. 
    
62.     def get_more_by_page(self, page, page_size):
    
63.         ''' 分页查询数据 '''
    
64.         offset = (page - 1) * page_size
    
65.         # 准备SQL
    
66.         sql = 'SELECT * FROM `news` WHERE `types` = %s ORDER BY `created_at` DESC LIMIT %s, %s;'
    
67.         # 找到cursor
    
68.         cursor = self.conn.cursor()
    
69.         # 执行SQL
    
70.         cursor.execute(sql, ('百家', offset, page_size))
    
71.         # print(dir(cursor))
    
72.         # 拿到结果
    
73.         rest = [dict(zip([k[0] for k in cursor.description], row))
    
74.             for row in cursor.fetchall()]
    
75.         # 处理数据
    
76.         # 关闭cursor/链接
    
77.         cursor.close()
    
78.         self.close_conn()
    
79.         return rest
    
80. 
    
81.     def add_one(self):
    
82.         ''' 插入数据 '''
    
83.         # 受影响的行数
    
84.         row_count = 0
    
85.         try:
    
86.             # 准备SQL
    
87.             sql = (
    
88.                 "INSERT INTO `news`(`title`,`image`, `content`, `types`, `is_valid`) VALUE"
    
89.                 "(%s, %s, %s, %s, %s);"
    
90.             )
    
91.             # 获取链接和cursor
    
92.             cursor = self.conn.cursor()
    
93.             # 执行sql
    
94.             # 提交数据到数据库
    
95.             cursor.execute(sql, ('标题11', '/static/img/news/01.png', '新闻内容5', '推荐', 1))
    
96.             # cursor.execute(sql, ('标题12', '/static/img/news/01.png', '新闻内容6', '推荐', 1))
    
97.             # 提交事务
    
98.             self.conn.commit()
    
99.         except :
    
100.             print('error')
     
101.             # 回滚事务
     
102.             self.conn.rollback()
     
103.         # 执行最后一条SQL影响的行数
     
104.         row_count = cursor.rowcount
     
105.         # 关闭cursor和链接
     
106.         cursor.close()
     
107.         self.close_conn()
     
108.         # row_count > 0 表示成功
     
109.         return row_count > 0
     
110. 
     
111. 
     
112. 
     
113. def main():
     
114.     obj = MysqlSearch()
     
115.     # rest = obj.get_one()
     
116.     # print(rest['title'])
     
117. 
     
118.     # rest = obj.get_more()
     
119.     # for item in rest:
     
120.     # print(item)
     
121.     # print('------')
     
122. 
     
123.     # rest = obj.get_more_by_page(2, 3)
     
124.     # for item in rest:
     
125.     # print(item)
     
126.     # print('------')
     
127. 
     
128.     rest = obj.add_one()
     
129.     print(rest)
     
130. 
     
131. 
     
132. if __name__ == '__main__':
     
133.     main()

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30523439/viewspace-2156930/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30523439/viewspace-2156930/