SQLite/python - Adding columns with data inside while loop

Question

I'm trying to build a SQLite database from scraped text. Each row in the database corresponds to a string taken from a list, and for every loop another column is created and populated with new string data.

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

cur.executescript('''
DROP TABLE IF EXISTS Data;
CREATE TABLE Data(
id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
words    text)''')

while True
    url = 'www.xyz.com'
    if url == "break": break

#parse - find tag of interest
    html = urllib.request.urlopen(url)
    p_s = BeautifulSoup(html,'html.parser')
    words = str(p_s.findAll('p',{'id':'p-5'}))
    words = strip_tags(words)
    words = pd.DataFrame(words)

    col_number = col_number + 1
    col_name = ('Group', col_number)

    cur.execute('''ALTER TABLE Data ADD ? TEXT''', (col_name,))
    for i,j in words.iterrows():
        cur.execute('''INSERT OR IGNORE INTO Data (col_name)
        VALUES (?)''',(j))
    conn.commit()

When I run this code I get:

 sqlite3.Operational.Error : near "?": syntax error

Where am I going wrong? Thanks, and I apologize for my sloppy code, I'm new to Python!

Adding new data as columns is terrible design. Add the "new string data" as new rows, not as new columns. Use one additional column Group to hold the col_name values. It'll be much easier to use the result. — alexis
– alexis, Commented Jul 17, 2017 at 23:23

codyc4321 · Accepted Answer · 2017-07-14 20:45:44Z

1

Change to:

    cur.execute('''ALTER TABLE Data ADD {} TEXT'''.format('Group ' + str(col_number)))
    for i,j in words.iterrows():
        cur.execute('''INSERT OR IGNORE INTO Data ({})
        VALUES (?)'''.format(col_name), (j,))
    conn.commit()

or

cur.execute('''ALTER TABLE Data ADD {} TEXT'''.format('Group ' + str(col_number)))
        for i,j in words.iterrows():
            cur.execute('''INSERT OR IGNORE INTO Data {}
            VALUES (?)'''.format(col_name), (j,))
        conn.commit()

one of those should work

edited Jul 14, 2017 at 20:45

answered Jul 14, 2017 at 19:35

codyc4321

9,78224 gold badges101 silver badges175 bronze badges

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

4 Comments

dooogan Over a year ago

Unfortunately neither are working for me. The return error is on the line: cur.execute('''ALTER TABLE Data ADD {} TEXT'''.format(col_name)) and the error is: sqlite3.Operational.Error: near "(": syntax error

codyc4321 Over a year ago

try cur.execute('''ALTER TABLE Data ADD {} TEXT'''.format('Group ' + col_number)) and see if it passes the first one

alexis Over a year ago

@cody Change that to 'Group ' + str(col_number) or equivalent, else you're adding a str to an int.

dooogan Over a year ago

Ok, thanks @alexis and cody, I've come up with a work around with your help. Instead of writing from a dataframe to the database, I wrote from a list to the database instead, and that seems to work using the above methods. Now I'm left with a problem where each time I append the database with a new list, the new data is still attached to the primary key from the first column. So my first column has 100 elements and ends at row 99, and my second column has 150 words but starts at row 100. Any idea how to work around this? I'd like each column to start at element row 1.

Collectives™ on Stack Overflow

SQLite/python - Adding columns with data inside while loop

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related