使用mysqlclient
先安装mysqlclient
网址：https://pypi.org/project/mysqlclient/
python中可使用pip安装，pip install mysqlclient
也可以使用别的方法，详细可查看之前的文章：
爬取百度百科词条写入数据库
python操作mysql
查询数据
import MySQLdb
# 获取连接
connection = MySQLdb.connect(
host = 'localhost',
user = 'root',
password = 'password',
db = 'school',
charset = 'utf8mb4',
port = 3306 # 默认3306，可不填port
)
# 获取数据
cursor = connection.cursor()
cursor.execute('SELECT * FROM `students`ORDER BY `in_time`DESC;')
result = cursor.fetchone() # 获取第一条数据
print (result)
# 关闭连接
connection.close()
可能会出现异常，所以改写
import MySQLdb
try:
# 获取连接
connection = MySQLdb.connect(
host = 'localhost',
user = 'root',
password = 'password',
db = 'school',
charset = 'utf8mb4',
port = 3306 # 默认3306，可不填port
)
# 获取数据
cursor = connection.cursor()
cursor.execute('SELECT * FROM `students`ORDER BY `in_time`DESC;')
result = cursor.fetchone() # 获取第一条数据
print (result)
except MySQLdb.Error as e:
print('Error : %s ' % e)
finally:
# 关闭连接
connection.close()
因为这个操作是所有都有的，所以封装成一个对象
import MySQLdb
class MysqlSearch:
def __init__(self):
self.get_connection()
def get_connection(self):
try:
self.connection = MySQLdb.connect(
host = 'localhost',
user = 'root',
password = 'password',
db = 'school',
charset = 'utf8mb4',
port = 3306 # 默认3306，可不填port
)
except MySQLdb.Error as e:
print('Error : %s ' % e)
def close_connection(self):
try:
if self.connection:
self.connection.close()
except MySQLdb.Error as e:
print('Error : %s ' % e)
def get_one(self):
# 获取会话指针
cursor = self.connection.cursor()
# 准备sql
sql = 'SELECT * FROM `students`WHERE`name`=%s ORDER BY `in_time`DESC;'
# 执行sql
cursor.execute(sql,('weilai',))
# print(cursor.description)
## (('id', 3, 1, 11, 11, 0, 0), ('name', 253, 6, 80, 80, 0, 0),
## ('nickname', 253, 4, 80, 80, 0, 1), ('sex', 254, 3, 4, 4, 0, 1),
## ('in_time', 12, 19, 19, 19, 0, 1))
## 获得一条结果
# a = [k[0] for k in cursor.description],
## a = ['id', 'name', 'nickname', 'sex', 'in_time']
# b = [k[0] for k in cursor.description],cursor.fetchone()
## (['id', 'name', 'nickname', 'sex', 'in_time'],
## (7, 'weilai', 'imwl', '男', datetime.datetime(2018, 12, 27, 22, 5, 41)))
result = dict(zip([k[0] for k in cursor.description],cursor.fetchone()))
# 关闭 cursor 和连接
cursor.close()
self.close_connection()
return result
def get_more(self):
cursor = self.connection.cursor()
sql = 'SELECT * FROM `students`WHERE`name`=%s ORDER BY `in_time`DESC;'
cursor.execute(sql,('weilai',))
result = [dict(zip([k[0] for k in cursor.description],row))
for row in cursor.fetchall()]
# print(result)
# [{'id': 7, 'name': 'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)},
# {'id': 8, 'name':'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)},
# {'id': 9, 'name': 'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)}]
cursor.close()
self.close_connection()
return result
def main():
obj = MysqlSearch()
# a = obj.get_one()
# print(a)
# print(a['id'])
b = obj.get_more()
for item in b:
print(item)
if __name__ == '__main__':
main()
# zip函数
'''
zip() 将对象中对应的元素打包成一个个元组，然后返回由这些元组组成的对象，元素个数与最短的一致
>>> a = [1,2,3]
>>> b = [4,5,6]
>>> c = [4,5,6,7,8]
>>> zipped = zip(a,b) # 返回一个对象
>>> zipped
<zip object at 0x103abc288>
>>> list(zipped) # list() 转换为列表
[(1, 4), (2, 5), (3, 6)]
>>> list(zip(a,c)) # 元素个数与最短的列表一致
[(1, 4), (2, 5), (3, 6)]
>>> a1, a2 = zip(*zip(a,b)) # 与 zip 相反，zip(*) 可理解为解压，返回二维矩阵式
>>> list(a1)
[1, 2, 3]
>>> list(a2)
[4, 5, 6]
'''
** 补充(分页查询)：
def get_more_by_pages(self, page, page_size):
# 分页查询数据
offset = (page -1) * page_size
cursor = self.connection.cursor()
sql = 'SELECT * FROM `students`WHERE`name`=%s ORDER BY `in_time`DESC LIMIT %s , %s;'
cursor.execute(sql,('weilai', offset, page_size))
result = [dict(zip([k[0] for k in cursor.description],row))
for row in cursor.fetchall()]
# print(result)
# [{'id': 7, 'name': 'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)},
# {'id': 8, 'name':'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)},
# {'id': 9, 'name': 'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)}]
cursor.close()
self.close_connection()
return result
新增/修改数据到数据库
出现问题不应该提交
def add_one(self):
# 准备SQL
try:
sql = (
"INSERT INTO `students` (`name`,`nickname`,`sex`,`in_time`) VALUE"
"(%s,%s,%s,%s);"
)
cursor = self.connection.cursor()
# 可以提交多条
cursor.execute(sql,('name1', 'nickname1', '男', None))
cursor.execute(sql,('name2', 'nickname2', '男', 'haha'))
# 提交事务
self.connection.commit()
# 关闭cursor和连接
cursor.close()
except MySQLdb.Error as e:
print('Error : %s ' % e)
self.connection.rollback()
self.close_connection()
网友评论