#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql
class Pysql(object):
def __init__(self):
self.get_conn()
def get_conn(self):
try:
self.a = 1
self.conn = pymysql.connect(
host='',
port=3306,
user='root',
password='',
charset='utf8',
database='school'
)
except pymysql.Error as e:
print(e)
def close_conn(self):
try:
if self.conn:
self.conn.close()
except pymysql.Error as e:
print(e)
def get_one(self):
"""
:return: 获取数据库中的一条数据返回这条数据的title字段
"""
# 准备sql
sql = 'SELECT * FROM `news` ORDER BY `create_at` DESC;'
# 获取cursor
cursor = self.conn.cursor()
# 执行sql
cursor.execute(sql)
# 拿到结果 dict(zip(['a','b'],(1,2))) ==> {'a': 1, 'b': 2} cursor.fetchone 拿到的是这条数据的元祖格式
res = dict(zip([x[0] for x in cursor.description],cursor.fetchone()))
# 处理结果
print(res['title'])
# 关闭cursor/连接
cursor.close()
self.close_conn()
def get_more(self,page,page_size):
"""
:param page: 显示当前页面
:param page_size: 每页显示的行数
:return: 获取数据库中的多条数据的title字段
"""
# 准备sql
offset = (page - 1) * page_size
sql = 'SELECT * FROM `news` WHERE `types`=%s ORDER BY `create_at` DESC LIMIT %s,%s;'
# 获取cursor
cursor = self.conn.cursor()
# 执行sql
cursor.execute(sql,('腾讯',offset,page_size))
# 拿到结果
# print(cursor.fetchall())
res = [dict(zip([x[0] for x in cursor.description],row)) for row in cursor.fetchall()]
# 处理结果
for ele in res:
print(ele['title'])
# 关闭cursor/连接
cursor.close()
self.close_conn()
def add_one(self):
try:
# 准备sql
sql = ("INSERT INTO `news`(`title`,`types`,`content`,`image`,`is_valid`) VALUES"
"(%s,%s,%s,%s,%s);"
)
# 获取cursor
cursor = self.conn.cursor()
# 执行sql
cursor.execute(sql,('C1','阿里','我是C1','/static/image/c1.img','1'))
cursor.execute(sql,('C1','阿里','我是C1','/static/image/c1.img','1',1))
# 提交事务
self.conn.commit()
except:
print('error')
self.conn.commit() # 如果上面的提交有错误,那么只执行对的那一个提交
# self.conn.rollback() # 如果有错误,就回滚
# 关闭连接
cursor.close()
self.close_conn()
def main():
sql = Pysql()
# sql.get_more(3,2)
sql.add_one()
if __name__ == '__main__':
main()
pymysql操作mysql的基本封装
最新推荐文章于 2023-04-25 14:40:39 发布