回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我正在开发一个python应用程序,其中连接到现有的SQLite数据库和表。我有一个CSV文件,每行包含一个值(stocks符号),我想使用python将所有值导入现有的表列“symbol”。
我该怎么做</p>
<p>我尝试的是定义一个插入函数:</p>
<pre><code>def insert(symbol):
conn=sqlite3.connect("stocks.db")
cur=conn.cursor()
cur.executemany("INSERT INTO stocks (symbol) VALUES (?)", (symbol,))
conn.commit()
conn.close()
</code></pre>
<p>然后尝试循环输入值并从中添加值:</p>
<pre><code>with open("./stocks/sp500_tickers.csv") as data:
tickers = data.read()
for ticker in tickers:
insert(ticker)
</code></pre>
<p>但是,当我运行python代码时,会出现以下错误:</p>
<pre><code>Traceback (most recent call last):
File "fetch.py", line 64, in <module>
insert(ticker)
File "fetch.py", line 23, in insert
cur.execute("INSERT INTO stocks (symbol) VALUES (?)", (symbol,))
sqlite3.IntegrityError: UNIQUE constraint failed: stocks.symbol
</code></pre>
<p>如果我这样尝试,也会发生同样的事情:</p>
<pre><code>with open("./stocks/sp500_tickers.csv") as data:
tickers = data.read()
rows = tickers.split('\n')
formatted = [tuple(x.split()) for x in rows]
for ticker in tickers:
insert(ticker)
</code></pre>
<p>该表的架构为:</p>
<pre><code> id INTEGER NOT NULL,
symbol VARCHAR,
name VARCHAR,
price NUMERIC(10, 2),
book_value NUMERIC(10, 2),
trailing_pe NUMERIC(10, 2),
forward_pe NUMERIC(10, 2),
trailing_eps NUMERIC(10, 2),
forward_eps NUMERIC(10, 2),
dividend_yield NUMERIC(10, 2),
ma50 NUMERIC(10, 2),
ma200 NUMERIC(10, 2),
sector VARCHAR,
growth_rate NUMERIC(10, 2),
PRIMARY KEY (id)
);
CREATE INDEX ix_stocks_id ON stocks (id);
CREATE UNIQUE INDEX ix_stocks_symbol ON stocks (symbol);
</code></pre>
<p>我的“sp500_tickers.csv”看起来像这样:</p>
<pre><code>MMM
ABT
ABBV
ABMD
etc
</code></pre>
<p>欢迎任何帮助</p>