python numpy mysql_python-将Numpy数组插入Mysql数据库

从anwser开始：

import numpy as np

import pylab as pl

import urllib

url = "http://ichart.yahoo.com/table.csv?a=2&c=2011&b=30&e=7&d=7&g=d&f=2011&s=msft&ignore=.csv"

f = urllib.urlopen(url)

title = f.readline().strip().split(",")

data = np.loadtxt(f, dtype=np.float, delimiter=",", converters={0: pl.datestr2num})

我想将返回行插入数据库.

数据如下所示：

[[734233.0 25.98 26.31 25.86 26.15 65581400 25.98]

[734232.0 25.82 26.18 25.74 25.78 73694500 25.61]

[734231.0 25.45 25.66 25.41 25.55 35433700 25.38]

[734228.0 25.53 25.53 25.31 25.48 63114200 25.31]

[734227.0 25.60 25.68 25.34 25.39 63233700 25.22]

[734226.0 25.60 25.72 25.50 25.61 41999300 25.44]]

我如何将这个numpy数组解析为列表或表,以便可以插入数据库.请注意,所有行不是分开的,而是一行.数据库部分起作用.

data.tolist()不解析单行

寻找像

[[734233.0 ,25.98 ,26.31 ,25.86 ,26.15, 65581400, 25.98]

[734232.0, 25.82, 26.18, 25.74, 25.78, 73694500, 25.61]

[734231.0, 25.45 ,25.66, 25.41, 25.55, 35433700, 25.38]

[734228.0, 25.53, 25.53, 25.31, 25.48, 63114200, 25.31]

[734227.0, 25.60 ,25.68, 25.34, 25.39, 63233700, 25.22]

[734226.0, 25.60, 25.72, 25.50, 25.61, 41999300, 25.44]]

是否将“”替换为“,”工作？

解决方法:

>>> import sqlalchemy as sa

>>> import numpy as np

>>> import time, datetime

>>> import urllib

日期格式之间的转换.

>>> datestr2timestamp = lambda d: time.mktime(time.strptime(d,"%Y-%m-%d"))

>>> def npvector_to_sadict(vector):

... row = dict(zip(("open", "high", "low", "close", "volume", "adj_close"),

... vector[1:]))

... row['date'] = datetime.date.fromtimestamp(vector[0])

... return row

...

从网络资源加载数据：

>>> url = "http://ichart.yahoo.com/table.csv?a=2&c=2011&b=30&e=7&d=7&g=d&f=2011&s=msft&ignore=.csv"

>>> f = urllib.urlopen(url)

>>> title = f.readline().strip().split(",")

>>> data = np.loadtxt(f, dtype=np.float, delimiter=",", converters={0: datestr2timestamp})

定义数据库表的外观

>>> metadata = sa.MetaData()

>>> stockdata = sa.Table('stockdata', metadata,

... sa.Column('date', sa.Date),

... sa.Column('open', sa.Float),

... sa.Column('high', sa.Float),

... sa.Column('low', sa.Float),

... sa.Column('close', sa.Float),

... sa.Column('volume', sa.Float),

... sa.Column('adj_close', sa.Float))

连接到数据库.您可以将其更改为mysql：// user：password @ host /用于mysql数据库

>>> engine = sa.create_engine("sqlite:///:memory:")

仅用于演示,如果您已经创建了表,请跳过此步骤.

>>> metadata.create_all(engine)

将数据插入数据库：

>>> engine.execute(stockdata.insert(), [npvector_to_sadict(datum) for datum in data])

验证它是否已插入

>>> print data.shape[0], engine.execute(sa.select([sa.func.count(stockdata.c.close)])).scalar()

90 90

>>>

标签：arrays,python,mysql,database,numpy

来源： https://codeday.me/bug/20191208/2088601.html