CSJS软件平台开发笔记-python与MySQL数据库

1.把numpy数组写入mysql

2.从mysql导出一个完整数据表

pymysql是python3和mysql连接的一个库

# -*- coding: utf-8 -*-
import pymysql as ms
from numpy import *
import pandas as pd

# 链接数据库
try:
    con = ms.connect(
        host='127.0.0.1',
        user='root',
        passwd='23',
        port=3306,
        charset='utf8')
    # 数据库执行函数
    cur = con.cursor()
except Exception as e:
    print(e)
    exit()


def mysql2py(database, table):
    """
    从数据库取数据
    :param database: 数据库名，字符串
    :param table: 表名，字符串
    :return:data: 数据，14列numpy数组
    """
    cur.execute('use ' + database)
    cur.execute('select * from '+table)
    data = array(cur.fetchall())
    con.commit()
    return data


def py2mysql(data, database, table):
    """
    写数据到数据库
    :param data: 数据，14列numpy数组
    :param database: 数据库名，字符串
    :param table: 表名， 字符串
    :return:
    """
    # 新建数据库，并连接
    sql = 'create database if not exists ' + database
    cur.execute(sql)
    cur.execute('use ' + database)
    # 新建数据表
    sql = 'create table ' + table + ' (' \
                                    '`station` double comment "测点号", ' \
                                    '`Ax` double comment "场源A坐标", ' \
                                    '`Ay` double comment "场源A坐标",' \
                                    '`Bx` double comment "场源B坐标", ' \
                                    '`By` double comment "场源B坐标", ' \
                                    '`Mx` double comment "测点M坐标", ' \
                                    '`My` double comment "测点M坐标", ' \
                                    '`Nx` double comment "测点N坐标", ' \
                                    '`Ny` double comment "测点N坐标", ' \
                                    '`f` double comment "频率", ' \
                                    '`I` double comment "电流", ' \
                                    '`V` double comment "电位差", ' \
                                    '`err` double comment "误差", ' \
                                    '`rho` double comment "视电阻率")'
    try:
        cur.execute(sql)
    except Exception as e:
        con.rollback()
        print(e)
    # 向数据表插入数据
    sql = "insert into " + table + " value (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    for i in range(data.shape[0]):
        values = data[i, 1:15]
        values = values.tolist()
        # 执行命令
        try:
            cur.execute(sql, values)
        except Exception as e:
            con.rollback()
            print(e)
    con.commit()


def closemysql():
    """
    关闭数据库连接
    :return:
    """
    con.close()


if __name__ == '__main__':
    f1 = open(r'F:\GSCJ\python' + '\\xy.dat')
    temp1 = pd.read_csv(f1, sep='\s+')
    data = array(temp1)
    py2mysql(data, 'sz', 'l2')
    mysql2py('nj', 'l1')
    closemysql()