Sqlite3 python adding columns and updating values
I am learning how to use sqlite3 in python. I have a simple table with two columns: id and name.
I tried to add a new column to this table using the following commands (I am working in ipython):
conn = sqlite3.connect('mydatabase.db')
c = conn.cursor()
c.execute("alter table studentinfo add column Group integer")
I am getting the following error:
OperationalError: near "Group": syntax error
Then based on the examples here on SO I tried,
c.execute("alter table studentinfo add column 'Group' integer")
It worked. However, I have a different problem now. Apparently the column name is "Group" instead of "Group".
For example, when I try to update a value in this column, from the following three commands, one works and two does not.
conn = sqlite3.connect('mydatabase.db')
c = conn.cursor()
c.execute("update studentinfo set Group=1 where ID <= 4") #This did not work.
I am getting the following error:
OperationalError: near "Group": syntax error
Then I tried to put quotes around the column names:
c.execute("update studentinfo set 'Group'=1 where 'ID' <= 4")
#This did not work either. Gives no error, but does not do anything. Records remain
#unchanged.
Then I tried using quotes around Group
but not around ID
. This works great.
c.execute("update studentinfo set 'Group'=1 where ID <= 4") #This worked fine.
That is, it thinks of the column name as "Group" (with quotes). How do I add a column named Group?
Thank.
source to share
The problem lies in the way you ran the ALTER TABLE command. Including single quotes around the column name you specified that was part of the name. Remove the quotes and it should work as you expect.
FYI: you can query the schema in sqlite3 with the dot-s (.s) command. It will show you the names of the true columns. Here's an example:
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a( mycol1 INT );
sqlite> alter table a add column mycol2 int;
sqlite> alter table a add column 'mycol3' int;
sqlite> .s
CREATE TABLE a( mycol1 INT , mycol2 int, 'mycol3' int);
sqlite>
source to share