Python 操作数据库（2）python和MySQL一些操作(连接、查询、修改)

一、Mysql

1.环境配置

• 虚拟环境 Virtualenv
• pip install mysqlclient
   

2.Python连接MySQL数据库

• 导入包 import MySQLdb
• 创建连接
• 连接测试，获取数据
• 生成cursor对象，可以执行操作命令和接受返回值两类方法，例如：
  - execute()
  - fetchall()/fetchone()
• 关闭连接，节约资源
• 加入异常，保证可读性
  • MySQLdb.Error 包含了全部的异常报错，但不包含警告

import MySQLdb
#创建连接
try:
	conn = MySQLdb.connection(
		host = '127.0.0.1',
		port = 3306
		user = 'root',
		passwd = '',
		db = 'news',
		charset = 'utf8'
	)
	# 生成游标
	cursor = conn.cursor()
	cursor.execute('SELECT * FROM `news` WHERE `id` < 5 ORDER BY DESC;')
	print(cursor.fetchone())
	# 关闭连接
	conn.close()
# 异常处理
except MySQLdb.Error as e:
	print('Error : %s' % e)
	

 

3.Python查询MySQL数据库

(1) 封装数据库查询操作，方便多次重复利用

• class 一个数据库查询类
  • 封装进 数据库连接方法connect( )
  • 封装进 数据库关闭方法 close( )
  • 封装了主方法 main（） ，执行入口

（2）封装获取数据方法，get_one（）和get_more（）

• 封装一个get_one方法，获得想要获得的新闻的标题，返回该值
  - 按照“准备SQL"、“找到cursor”、“执行SQL”、“拿到结果”、“处理数据”、“关闭cursor/连接"步骤进行
  - 需了解光标cursor的一些用法

• 处理数据：获得查询出的文章的标题：
  - 使用了字典生成函数dict( ),
  -使用了 zip( )函数，使用可迭代的对象生成包含元组的列表
  -列表推导式

• 封装一个get_more()方法

• get_more（）扩展，分页

import MySQLdb


class MysqlSearch(object):

    # 初始化
    def __init__(self):
        self.get_conn()

    # 创建连接
    def get_conn(self):
        try:
            self.conn = MySQLdb.connect(
                host='127.0.0.1',
                user='root',
                passwd="",
                db='news',
                port=3306,
                charset='utf8'
            )


        except MySQLdb.Error as e:
            print('Error: %s' % e)

    # 关闭连接
    def get_close(self):
        try:
            if self.conn:
                self.conn.close()

        except MySQLdb.Error as e:
            print('Error: %s' % e)

    # 获取一条数据
    def get_one(self):
        # 准备SQL
        sql = 'SELECT * FROM `news` WHERE `types` = %s ORDER BY `created_at` DESC;'

        # 找到cursor
        cursor = self.conn.cursor()
        # 执行SQL
        cursor.execute(sql, ('百家',))

        # 拿到结果, 处理数据
        rest = dict(zip([k[0] for k in cursor.description], cursor.fetchone()))

        # 关闭cursor/连接
        cursor.close()
        self.get_close()
        return rest

    # 获取多条数据
    def get_more(self):
        # 准备SQL
        sql = 'SELECT * FROM `news` WHERE `types` = %s ORDER BY `created_at` DESC;'

        # 找到cursor
        cursor = self.conn.cursor()
        # 执行SQL
        cursor.execute(sql, ('百家',))

        # 拿到结果, 处理数据
        rest = [dict(zip([k[0] for k in cursor.description], row))
                for row in cursor.fetchall()]

        # 关闭cursor/连接
        cursor.close()
        self.get_close()
        return rest

    # 获取多条数据,并分页
	def get_more_limit(self, title, page, page_size):
        # 准备SQL
        offset = int((page - 1) * page_size)

        sql = 'SELECT * FROM `news` WHERE `types` = %s ORDER BY `created_at` DESC LIMIT %s,%s;'

        # 找到cursor
        cursor = self.conn.cursor()
        # 执行SQL
        cursor.execute(sql, (title, offset, page_size))

        # 拿到结果, 处理数据,rest最后是列表
        rest = [dict(zip([k[0] for k in cursor.description], row))
                for row in cursor.fetchall()]

        # 关闭cursor/连接
        cursor.close()
        self.get_close()
        return rest


def main():
    obj = MysqlSearch()
    rest = obj.get_more_limit('NBA',2, 2)
    print(rest)


if __name__ == '__main__':
    main()

---

 

4.Python更新MySQL数据库

修改数据、插入数据

类似查询操作：

1. 准备SQL
2. 获取连接和cursor
3. 执行SQL
4. 提交到数据库 commit( )
5. 关闭连接、cursor

 

需要注意事项：

• 单个事务，或者多条事务，执行要么成功，要么失败
• 失败的要么提交部分，要么全部不提交
• 因此需要 try: … except：… 操作，处理异常报警问题
• 在except中提交 commit( )，是提交正确的部分；
• 在except中回滚rollback( ),是全部不提交

    # 插入数据
    def add_one(self):
        try:
            # 准备SQL
            sql = (
                "INSERT INTO `news`(`title`,`types`,`content`,`is_valid`) VALUE  "
                "(%s,%s,%s,%s);"
            )
            # 获取连接和cursor
            cursor = self.conn.cursor()
            # 执行SQL
            # 提交数据到数据库
            cursor.execute(sql, ('华为12', '科技新闻7', '如果你是一位英国智能手机用户', 1))
            cursor.execute(sql, ('华为13', '科技新闻8', '如果你是一位英国智能手机用户', '推荐', 'dw'))

            # 提交事务,将缓存中的数据提交到数据库
            self.conn.commit()
            # 关闭连接和cursor
            cursor.close()
            self.get_close()
        except:
            print('error')
            #self.conn.commit()    提交正确的
            # 回滚，一条出错全部不提交
            self.conn.rollback()


def main():
    obj = MysqlSearch()
    # rest = obj.get_more_limit('NBA', 2, 2)
    # print(rest)
    obj.add_one()

---

扩展尝试了删除操作，删除标题为“华为12”并且id大于46的内容。

刚开始这样写执行SQL：
cursor.execute( sql, ( ‘华为12’ ) )
或者
cursor.execute( sql, ‘华为12’ )
都会报错：
MySQLdb._exceptions.ProgrammingError: not all arguments converted during byt

 

经过百度后，发现要这样写，可以正确删除相应内容：

cursor.execute( sql, ( '华为12 ', ) )
大意是%操作符只能直接用于字符串、列表、元组，因此要制造一个元组。

sql = (
                "DELETE  FROM `news` WHERE `title`= %s AND `id` > 46 "
            )
        # 获取连接和cursor
        cursor = self.conn.cursor()
        # 执行SQL
        # 提交数据到数据库
        cursor.execute(sql, ('华为12',))
        # cursor.execute(sql, ('华为13', '科技新闻8', '如果你是一位英国智能手机用户', '推荐', 'dw'))