import data from text/csv file into sqlite table using python

Question

I am working on a python application in which I connect to the existing SQLite database and table. I have a CSV file containing one value per row (stocks symbol) and I want to import all the values into the existing table column "symbol" using python. How can I do this?

What I've tried is defining an insert function:

def insert(symbol):
    conn=sqlite3.connect("stocks.db")
    cur=conn.cursor()
    cur.executemany("INSERT INTO stocks (symbol) VALUES (?)", (symbol,))
    conn.commit()
    conn.close()

and then try looping in the value and adding the values from there:

with open("./stocks/sp500_tickers.csv") as data:
      tickers = data.read()
      for ticker in tickers:
          insert(ticker)

But when I run the python code, I get this error:

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

The same thing happens if I try like this:

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)

The schema for the table is:

        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);

My "sp500_tickers.csv" looks something like this:

MMM
ABT
ABBV
ABMD
etc

Any help is very welcomed!

If insert fails, use update. For that you will have to perform individual transactions not a batch ttransaction. — Rolf of Saxony
– Rolf of Saxony, Commented Sep 15, 2020 at 9:01
Plus, never underestimate the power of the print() command to debug stuff like this i.e. print("inserting", symbol) . It pays to know what you are asking the code to do. — Rolf of Saxony
– Rolf of Saxony, Commented Sep 15, 2020 at 12:15

Matei Florescu · Accepted Answer · 2020-09-15 08:49:37Z

1

From the error message

sqlite3.IntegrityError: UNIQUE constraint failed: stocks.symbol

it seems like the values you are trying to insert into the symbol column are not unique. There is an unique index defined for that column.

CREATE UNIQUE INDEX ix_stocks_symbol ON stocks (symbol);

You should either make sure the values you are inserting are unique or drop the unique index.

answered Sep 15, 2020 at 8:49

Matei Florescu

1,19511 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jbeginner Over a year ago

thank you for the comment, however I think there is something else wrong, I've made sure I don't have anything into the table and only tried with 2 values in the csv file and the same error occurs.

DinoCoderSaurus · Accepted Answer · 2020-09-15 11:10:03Z

0

This tickers = data.read() makes tickers one big string. This for ticker in tickers: iterates over each character in the string. Second attempt would fix that problem, if not for the typo here for ticker in tickers:

Once the input is converted to a list, there is actually no reason to iterate over it; that sort of defeats the purpose of executemany :)

edited Sep 15, 2020 at 11:10

answered Sep 15, 2020 at 11:04

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

Collectives™ on Stack Overflow

import data from text/csv file into sqlite table using python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related