How to insert a list of values into a column in sqlite3 with python?

Question

I can't seem to get executemany to work. I didn't get any errors. But no values got inserted either. The following is a small example: the table prices already has some columns and 3 rows. I added a new column t and inserted the values in vals into t.

vals = [(1,), (2,), (3,)] 
cursor.execute('ALTER TABLE prices ADD COLUMN t REAL') #"
cursor.executemany("INSERT INTO TABLE prices(t) VALUES(?)", vals)
data = cursor.execute("SELECT id, t FROM prices")

for i in data:
    print i

I get

(1, None)
(2, None)
(3, None)

Your syntax for insert seems to be incorrect. Please copy-paste a short, complete program that demonstrates the error See minimal reproducible example for more info. — Robᵩ
– Robᵩ, Commented Aug 11, 2016 at 19:27
When I fix your syntax error and add the appropriate wrapper code, I cannot reproduce your result. See here for your code working perfectly. — Robᵩ
– Robᵩ, Commented Aug 11, 2016 at 19:29

APA · Accepted Answer · 2016-08-11 19:24:34Z

1

it does't look like you're inserting values into the column 't'.i'm assuming your table has 2 columns (id,t) Check your list on top which has

vals = [(1,), (2,), (3,)]

perhaps that's the problem. Could you elaborate a little more?

answered Aug 11, 2016 at 19:24

APA

5732 gold badges7 silver badges17 bronze badges

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

1 Comment

tnabdb Over a year ago

Actually, my table has more than 2 columns. Column t is the last one added. I stated this in my post.

William Miller · Accepted Answer · 2021-02-11 18:57:28Z

0

You have actually successfully inserted the values into the table. The problem is that they are new rows at the bottom. If you do a fetch all you'll have

(col1, col2, ..., none)
.
.
.
(none, none, ..., 1)

this is because the INSERT INTO command adds rows.

Rob's example worked because he didn't have existing columns in the table.

You need to update with a WHERE clause: although this is slow and I'd like to speed up

add_col_str = 'ALTER TABLE orig ADD COLUMN {} INT'.format(col_name)
db.execute(add_col_str) 

for i in range(len(df)):
    add_data_str = "UPDATE orig SET {} =(?) WHERE orig_id=(?)".format(col_name)
    value = int(df.values[i])
    idx = df.index[i]
    db.execute(add_data_str, (value, idx))
# commit
db.commit()

edited Feb 11, 2021 at 18:57

William Miller

10.4k4 gold badges31 silver badges50 bronze badges

answered Sep 23, 2017 at 16:01

Tom Logan

3513 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to insert a list of values into a column in sqlite3 with 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