Mysql—Python连接第三方数据库系列之使用pymysql连Mysql
Redis—Python连接第三方数据库系列之使用redis连Redis
MongoDB—Python连接第三方数据库系列之使用pymongo连接MongoDB

安装连接Mysql的Python库—Pymysql

pip install pymysql -i  https://pypi.mirrors.ustc.edu.cn/simple/

数据库连接

1.打开数据库连接 db = pymysql.connect(“localhost”,“root”,“root”,"dbName“)
2.创建游标对象 cursor = db.cursor()
3.执行SQL语句 cursor.execute(sql)
4.获取数据 fetchone()、fetchall()
5.关闭游标和数据库连接 db.close()、cursor.close()

数据库查询：

python查询fetchone()方法获取单条数据,使用fetchall()方法获取多条
fetchone()获取单条数据
fetchall()获取全部结果行
rowcount()返回执行execute()方法影响的行数

1、数据库连接

import pymysql
import datetime

# 数据库连接
def db_connect():
    #1.打开数据库连接
    #   主机名:localhost
    #   用户名:root
    #   密  码:root
    #   数据库名:test
    db = pymysql.connect("localhost","root","root","test")
    #2.使用cursor()方法创建一个游标对象cursor
    cursor = db.cursor()
    #3.使用execute()方法执行SQL查询
    cursor.execute("select version()")
    #4.使用fetchone()方法获取单条数据
    data = cursor.fetchone()
    print(f"Database version: {data[0]}")
    #5.关闭数据库连接
    cursor.close()
    db.close()

2、创建表

# 创建表
def create_table():
    # 1.创建数据库连接
    db = pymysql.connect("localhost", "root", "123456", "test")
    # 2.创建游标对象
    cursor = db.cursor()
    # 3.执行sql语句
    cursor.execute("drop table if exists employee")
    sql = """
        create table employee(
        first_name char(20) not null,
        last_name char(20),
        age int,
        sex char(1),
        income float,
        ctrate_time datetime)
    """
    try:
        cursor.execute(sql)
        print("create table success")
    except Exception as e:
        print(e)
    finally:
        # 关闭游标连接数据库连接
        cursor.close()
        db.close()

3、插入数据

# 插入数据
def insert_record():
    # 1.创建数据库连接
    db = pymysql.connect("localhost","root","123456","test")
    # 2.创建游标对象
    cursor = db.cursor()
    # 3.插入sql语句
    sql = "insert into employee(first_name,last_name,age,sex,income,"\
            "ctrate_time)values ('%s','%s','%d','%c','%d','%s')"\
          %('xiao','zhi',22,'M',30000,datetime.datetime.now())
    try:
        cursor.execute(sql)
        # 3.1 将插入信息提交到数据库执行
        db.commit()
        print("insert infos success")
    except Exception as e:
        print(e)
        # 3.2 未插入成功则回滚
        db.rollback()
    finally:
        db.close()

4、数据库查询

# 数据表查询
def query_data():
    # 1.打开数据库连接
    db = pymysql.connect("localhost","root","123456","test")
    # 2.创建游标对象
    cursor = db.cursor()
    # 3.插入sql语句
    sql = "select * from employee where income > %d" % 10000
    try:
        cursor.execute(sql)
        # 获取所有记录,返回的是一个元组,内部包含其他元组
        results = cursor.fetchall()
        print(results)
        for row in results:
            print(row)
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        db.close()

5、数据表更新

# 数据表更新
def update_table():
    # 1.创建数据库连接
    db = pymysql.connect("localhost","root","123456","test")
    # 2.创建游标对象
    cursor = db.cursor()
    # 3.插入sql语句
    sql = "update employee set age = age+1 where sex = '%s'" % 'M'
    try:
        cursor.execute(sql)
        db.commit()
        print("update data success")
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        cursor.close()
        db.close()

6、数据表删除

# 数据表删除
def delete_record():
    # 1.创建数据库连接
    db = pymysql.connect("localhost","root","123456","test")
    # 2.创建游标对象
    cursor = db.cursor()
    # 3.插入sql语句
    sql = "delete from employee where age > %d"% 22
    try:
        cursor.execute(sql)
        db.commit()
        print("delete data success")
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        cursor.close()
        db.close()