How to add columns to sqlite3 python?

Question

I know this is simple but I can't get it working! I have no probs with insert,update or select commands, Lets say I have a dictionary and I want to populate a table with the column names in the dictionary what is wrong with my one line where I add a column?

##create
con = sqlite3.connect('linksauthor.db')
c = con.cursor()
c.execute('''create table linksauthor (links text)''')
con.commit()
c.close()
##populate author columns
allauthors={'joe':1,'bla':2,'mo':3}
con = sqlite3.connect('linksauthor.db')
c = con.cursor()
for author in allauthors:
    print author
    print type(author)
    c.execute("alter table linksauthor add column '%s' 'float'")%author  ##what is wrong here?
    con.commit()
c.close()

balpha · Accepted Answer · 2010-04-22 18:11:29Z

18

Your paren is misplaced. You probably meant this:

c.execute("alter table linksauthor add column '%s' 'float'" % author)

answered Apr 22, 2010 at 18:11

balpha♦

51.2k18 gold badges114 silver badges133 bronze badges

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

Comments

Frank Krueger · Accepted Answer · 2010-04-22 18:15:28Z

3

You are also using strings for the column name and type name. Sqlite is very forgiving, but you really should be using double-quotes as the quoting character for identifiers.

answered Apr 22, 2010 at 18:15

Frank Krueger

71.2k48 gold badges165 silver badges212 bronze badges

Comments

Egidius · Accepted Answer · 2021-08-13 11:10:15Z

1

Here is another option:

c.execute(
f"""
ALTER TABLE linksauthor 
ADD COLUMN {author} 'float'
""")

answered Aug 13, 2021 at 11:10

Egidius

1,5892 gold badges16 silver badges24 bronze badges

Comments

Flair · Accepted Answer · 2021-08-15 06:08:43Z

0

The other answers are suggesting Python's built-in string manipulation which is insecure and can lead to SQL injection. I recommend the DB-API’s parameter substitution: https://docs.python.org/3/library/sqlite3.html?highlight=parameter%20substitution

c.execute("alter table linksauthor add column (?) 'float'", (author,))

answered Aug 15, 2021 at 6:08

Flair

2,9572 gold badges33 silver badges45 bronze badges

2 Comments

leftaroundabout Over a year ago

This unfortunately gives OperationalError: near "(": syntax error, though.

leftaroundabout Over a year ago

stackoverflow.com/a/75399761/745903

Collectives™ on Stack Overflow

How to add columns to sqlite3 python?

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related