Python操作MySQL数据库 - PyMySQL库的基本用法

# !/usr/bin/env python
# -*- coding:utf-8 -*-

"""
使用PyMySQL操作MySQL数据库
    安装库: pip3 install PyMySQL

    支持的版本:
        python>=2.6,或者python>=3.3
        mysql>=4.1


    数据库语句
        create database mydb;

    建表语句
        create table mytable(
            id int not null auto_increment primary key,
            name varchar(15) not null,
            age tinyint not null,
            sex varchar(5) not null,
            phone char(11),
            info text);

    建议使用with上下文管理器操作cursor对象.

"""

import pymysql

# 连接到数据库,获取Connection对象
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='123456',
                             db='mydb',
                             charset='utf8',        # 不能用utf-8
                             cursorclass=pymysql.cursors.DictCursor)

try:
    # 插入
    # 获取Cursor对象
    with connection.cursor() as cursor:
        sql = "INSERT INTO mytable (name, age, sex, phone, info) VALUES (%s, %s, %s, %s, %s)"
        cursor.execute(sql,('比尔盖茨一号', '99', '男', '12000000000', '比尔盖茨是土豪'))
        cursor.execute(sql,('比尔盖茨二号', '99', '男', '12000000000', '比尔盖茨是土豪'))


    print('——'*30)
    # 查询
    with connection.cursor() as cursor:
        sql = "SELECT * FROM mytable"
        cursor.execute(sql)
        result = cursor.fetchone()
        print(result)
        result = cursor.fetchone()
        print(result)


    print('——' * 30)
    # 修改
    with connection.cursor() as  cursor:
        sql="UPDATE mytable SET age=%s,info=%s where name=%s"
        cursor.execute(sql,(66,'比尔盖茨是美国的土豪','比尔盖茨一号'))

        cursor.execute(query='SELECT * FROM mytable')
        result=cursor.fetchall()
        for item in result:
            print(item)


    print('——' * 30)
    # 删除
    with connection.cursor() as cursor:
        sql="DELETE FROM mytable WHERE name=%s"
        cursor.execute(sql,('比尔盖茨二号',))

        cursor.execute(query='SELECT * FROM mytable')
        result = cursor.fetchall()
        for item in result:
            print(item)

except:
    # 回滚事务
    connection.rollback()
finally:
    # 提交(默认不开启自动提交)
    connection.commit()
    # 断开连接
    connection.close()

运行结果

/usr/bin/python3.5 /home/brandon/PythonProjects/MySpider/数据存储/保存到数据库/MySQL/使用PyMySQL库操作MySQL数据库.py
————————————————————————————————————————————————————————————
{'id': 40, 'phone': '12000000000', 'age': 99, 'info': '比尔盖茨是土豪', 'name': '比尔盖茨一号', 'sex': '男'}
{'id': 41, 'phone': '12000000000', 'age': 99, 'info': '比尔盖茨是土豪', 'name': '比尔盖茨二号', 'sex': '男'}
————————————————————————————————————————————————————————————
{'id': 40, 'phone': '12000000000', 'age': 66, 'info': '比尔盖茨是美国的土豪', 'name': '比尔盖茨一号', 'sex': '男'}
{'id': 41, 'phone': '12000000000', 'age': 99, 'info': '比尔盖茨是土豪', 'name': '比尔盖茨二号', 'sex': '男'}
————————————————————————————————————————————————————————————
{'id': 40, 'phone': '12000000000', 'age': 66, 'info': '比尔盖茨是美国的土豪', 'name': '比尔盖茨一号', 'sex': '男'}

Process finished with exit code 0