python开发cs软件_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()