Python连接mysql数据库

Python中连接MySQL的库主要有三个，Python-MySQL，PyMySQL和SQLAlchemy，其中Python-MySQL已经停止更新，且只支持Python2，目前使用最广泛的是PyMySQL,使用方法如下：

安装

pip install pymysql

连接

代码中只需要 import pymysql 即可导入使用，pymysql的一般使用步骤为：

1. 配置数据库连接信息
2. 连接数据库，获取连接对象
3. 使用连接对象获取一个游标（cursor)对象
4. 使用cursor对象提供的方法执行SQL语句
5. 关闭cursor对象
6. 关闭连接对象

操作

以下为增删改查的实例:

• 查询函数, 传入键值进行查询并返回结果列表

def find(key, word):
    con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')
    cursor = con.cursor()
    sql = "select * from main_info where {}='{}'".format(key, word)
    list = []
    try:
        cursor.execute(sql)
        row = cursor.fetchone()
        while row:
            print(row)
            list.append(row)
            row = cursor.fetchone()
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        con.close()
        return list

• 插入函数, 插入新信息

def insert(id, name):
    con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')
    cursor = con.cursor()
    data = {
        'id': id,
        'name': name,
    }
    table = 'main_info'
    keys = ', '.join(data.keys())
    values = (', '.join('"' + item + '"' for item in data.values()))
    sql = "insert into {0}({1}) values({2})".format(table, keys, values)
    try:
        if cursor.execute(sql):
            con.commit()
            result = True
    except Exception as e:
        con.rollback()
        result = False
    finally:
        cursor.close()
        con.close()
        return result

• 删除函数， 以删除制定id为例

def delete(id):
    con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')
    cursor = con.cursor()
    
    sql = "delete from main——info where id = {}".format(id)
    try:
        if cursor.execute(sql):
            con.commit()
            result = True
    except Exception as e:
        con.rollback()
        result = False
    finally:
        cursor.close()
        con.close()
        return result

• 修改函数， 修改指定id的name部分

def modify(id, name):
    con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')
    cursor = con.cursor()
    
    sql = "update from main——info set name = {} where id = {}".format(name, id)
    try:
        if cursor.execute(sql):
            con.commit()
            result = True
    except Exception as e:
        con.rollback()
        result = False
    finally:
        cursor.close()
        con.close()
        return result