CODEWITHSUNDEEP
pythonsqlsqlite
martinrichardf
martinrichardf

Reputation: 23

A way to iterate creation of new SQL tables using a column from another SQL table?

My program aims to take values from an existing table and create a new table named from those values. I was able to check if it iterates with print() but once I input cur.execute(), the program only makes the first table and then stops. What am I missing?

import sqlite3


conn = sqlite3.connect('PSEIraw.sqlite')
cur = conn.cursor()

cur.execute('''SELECT STOCK_CODE FROM psei_raw''')
row = cur.fetchone()
for row in cur:
    stockcode='''CREATE TABLE IF NOT EXISTS "''' + row[0] + '''" (Date DATE, Open DECIMAL, High DECIMAL, Low DECIMAL, Close Decimal, Volume DECIMAL)'''
    cur.execute(stockcode)

conn.commit()

EDIT 1 I plan on getting at most 50 rows per stock for stdev computations and volume comparisons. I know that designwise, this isn't the best but that's the process i am in now. Gist is to download EOD for all stocks in our exchange (200+ stocks) and have OHLC and volume that inserted per stock table.

EDIT 2

Done. just iterated and stored in a list then iterated again to make tables.

Upvotes: 1

Views: 52

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

You can use cur.fetchall() rather as in the below code

cur.execute('''SELECT stock_code FROM psei_raw''')
row = cur.fetchall()
for r in row:
    stockcode='''CREATE TABLE IF NOT EXISTS "''' + str(r[0]) + '''"
                (
                 Date  DATE, 
                 Open  DECIMAL, 
                 High  DECIMAL, 
                 Low   DECIMAL, 
                 Close DECIMAL, 
                 Volume DECIMAL
                )
              '''
    cur.execute(stockcode)

where using conn.commit() is redundant because it's not a DML but a DDL statement.

Upvotes: 1

aneroid
aneroid

Reputation: 15962

Two problems here:

row = cur.fetchone()  # <-- This skips your first row in the loop below
for row in cur:       # <-- and then you iterate over the remaining rows
    ...

The 2nd problem when you fix that one:

You're re-using the same cursor object cur before you've got all your rows fetched. cur.execute(stockcode) will make the first cur object useless. (Or will get weird iteration since it's been mutated/changed mid-loop.)

Either:

  1. Fetch all rows first and then create all tables, or

  2. Use a 2nd cursor object to create the tables:

    conn = sqlite3.connect('PSEIraw.sqlite')
cur = conn.cursor()
cur.execute('''SELECT STOCK_CODE FROM psei_raw''')
# removed the line which was here cur.fetchone()

table_cur = conn.cursor()  # another cursor object
for row in cur:
    stockcode = '''CREATE TABLE...'''  # as before
    table_cur.execute(stockcode)

conn.commit()

Upvotes: 0

Related Questions