python连接数据库--MySQL

pymysql

首先申明版本，python == 3.6.5，pymysql == 0.9.3，pandas == 1.1.5 。

0.安装

命令行(windows的小伙伴，快捷键 win+R键，输入cmd)

pip install pymysql

1.读取数据

import pymysql
import pandas as pd

## 数据库参数
LOCAL_MYSQL_INFO = {
    'host':"172.16.30.70",
    'user':"root",
    'password':"xxxxxx",
    'port':3306,
    'database':"demand_info"
}

def get_openapp_info():
    db = pymysql.connect(**LOCAL_MYSQL_INFO)
    sql = """
    SELECT 
    a1.openapp_id,
    a1.open_time,
    a2.user_id
    FROM `openapp_relations` a1
    LEFT JOIN open_app a2 ON a1.openapp_id = a2.id
    WHERE a1.raw_number = 1 
    """
    openapp_info = pd.read_sql(sql,db)
    db.close()
    return openapp_info

if __name__ == "__main__":
    print(get_openapp_info())

接下来大家就可以用获取到的数据在python里尽情发挥了，切片啦~ 循环啦~ 跨库合并啦~等等。

2.写入数据

import pymysql
import pandas as pd

## 数据库参数
LOCAL_MYSQL_INFO = {
    'host':"172.16.30.70",
    'user':"root",
    'password':"xxxxxx",
    'port':3306,
    'database':"demand_info"
}

## 格式1：一步到位，输入的是dataframe数据格式，注意df的列名必须与数据库的字段名一致
def insert_data(df):
    df = df.where(df.notnull(), None)  ##将dataframe数据中的null值全部替换为None，否则存储会报错

    db = pymysql.connect(**LOCAL_MYSQL_INFO)
    cursor = db.cursor()
    data = df.values.tolist()
    keys = list(df.keys())
    key = ','.join(item for item in keys)
    values = ",".join(['%s'] * len(keys))
    insert_sql = "INSERT INTO mytable({0}) VALUES({1})"
    try:
        cursor.executemany(insert_sql.format(key, values), data)
        db.commit()
        save_result = True
    except Exception as ex:
        print(ex)
        db.rollback()  ## 回滚，释放表的占用，否则一旦提交的时候报错，会锁表
        save_result = False
    finally:
        cursor.close()
        db.close()        
    return save_result


## 格式2：df的列名必须与数据库的字段名不一致
def insert_data(df):
    df = df.where(df.notnull(), None)  ##将dataframe数据中的null值全部替换为None，否则存储会报错

    db = pymysql.connect(**LOCAL_MYSQL_INFO)
    cursor = db.cursor()

    insert_sql = "INSERT INTO mytable(name, col1, col2) VALUES(%s, %s, %s)"
    try:
        cursor.executemany(insert_sql, df.values.tolist())
        db.commit()
        save_result = True
    except Exception as ex:
        print(ex)
        db.rollback()  
        save_result = False
    finally:
        cursor.close()
        db.close()        
    return save_result

注意：一定要添加回滚。如果不添加报错回滚，一旦commit提交时报错，很可能就锁表了，接下来…你就啥命令都执行不了。血泪教训。

3.更新数据

（1）update

更新特定位置的数据。举个例子，用户表中，已知用户姓名，用户更新了家庭地址和邮箱。

def update_compititor_store_data(update_data):  
    
    db = pymysql.connect(**LOCAL_MYSQL_INFO)
    cursor = db.cursor() 
    sql = """
    UPDATE user_info 
    SET address = %s,
    email = %s
    WHERE name = %s    
    """
    try:
        cursor.executemany(sql, update_data)
        db.commit()    
    except Exception as ex:
        print(ex)
        db.rollback()  
    finally:
        cursor.close()
        db.close()

（2）replace

从功能上来说：replace = update + insert

一般与唯一索引同时使用。举个例子，一张用户信息表中，如果把身份证号设为唯一索引（也就是说这张表不允许出现有同一个身份证号的两条及以上数据，表中所有用户身份证号均不同）。

那么问题来了，有个朋友刚换了一个新手机号，不记得自己注没注册过，就用新手机号注册了。这个时候，repalce会怎么做呢？首先，replace会判断你新增的数据中身份证号mysql中是否已经存在，如果存在那就删除原来的那条数据再插入新增的数据，如果不存在那就直接插入新增的数据。

语法和insert相似。

## 格式1
def replace_data(df):
    df = df.where(df.notnull(), None)
    db = pymysql.connect(**LOCAL_MYSQL_INFO)
    cursor = db.cursor()
    data = df.values.tolist()
    keys = list(df.keys())
    key = ','.join(item for item in keys)
    values = ",".join(['%s'] * len(keys))
    sql = "REPLACE INTO user_info({0}) VALUES({1})"
    try:
        cursor.executemany(sql.format(key, values), data)
        db.commit()
    except Exception as ex:
        print("出错！！",ex)
        db.rollback()
    finally:
        cursor.close()
        db.close()        

## 格式2
def replace_data(df):
    df = df.where(df.notnull(), None)
    
    db = pymysql.connect(**LOCAL_MYSQL_INFO)
    cursor = db.cursor()
    
    data = df.values.tolist()
    sql = "REPLACE INTO user_info(name, id_number, phone) VALUES(%s, %s, %s)"
    try:
        cursor.executemany(sql, data)
        db.commit()
    except Exception as ex:
        print("出错！！",ex)
        db.rollback()
    finally:
        cursor.close()
        db.close()

4.补充

其他问题，如果是postgresql数据库

建议安装psycopg2_binary

pip install psycopg2_binary

导入模块

import psycopg2

其余用法基本与pymysql一致，注意一下，postgresql没有replace语法。