python中怎么利用 mysqldb类库操作数据库

这期内容当中小编将会给大家带来有关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()

上述就是小编为大家分享的python中怎么利用 mysqldb类库操作数据库了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。