sqlite3 Python delete column

Question

Wow, it is incredibly hard just to delete a column in sqlite and I have been trying 20 things for the last 2 days now.

At the moment I have some code that does not give me an error (hurrah!), but it also doesn't give me any results.

I am working of the SQL:FAQ way of thinking - http://www.sqlite.org/faq.html#q11 but I am stuck where my code runs without errors, but when I open the database with DB Browser, the new table only has the headings of the one I copied, but not the data.

Here is the code:

    backup = "backup"
    c.execute("CREATE TABLE IF NOT EXISTS {bu} (id INTEGER PRIMARY KEY AUTOINCREMENT, headline TEXT, datestamp TEXT, link TEXT)".format(bu=backup))
    c.execute("INSERT INTO {dt} SELECT {id}, {hl}, {ds}, {lk} FROM {on}".format(dt=backup, id="id", hl="headline", ds="datestamp", lk="link", on=old_table))

Like I said, it runs, but there is no data in the new table, only the headings of the columns.

Beyond frustrated, any help will be appreciated

Don't use string formatting on SQL statements! See https://docs.python.org/2/library/sqlite3.html for details. — Klaus D., Dec 01 '16 at 13:43

Steve D. · Accepted Answer · 2016-12-01T14:45:55.250

0

While your code snippet here has some other possible issues that may arise as per the comments you have received, to get the information to "show up" in the data base specifically, you need to finish the transaction.

backup = "backup"
c.execute("CREATE TABLE IF NOT EXISTS {bu} (id INTEGER PRIMARY KEY AUTOINCREMENT, headline TEXT, datestamp TEXT, link TEXT)".format(bu=backup))
c.execute("INSERT INTO {dt} SELECT {id}, {hl}, {ds}, {lk} FROM {on}".format(dt=backup, id="id", hl="headline", ds="datestamp", lk="link", on=old_table))
conn.commit()

Otherwise when you leave the "scope" of these variables (which may be the end of your script) the transaction will rollback as part of the tear down done by sqlite3.

edited Dec 01 '16 at 14:45

answered Dec 01 '16 at 14:11

Steve D.

251
2
5

Thank you Steve, I did try the commit earlier as the link shows, but it ended up giving me an error. And then on the link that Klaus provided I see they used conn.commit(), which was what I needed. (I know that you did not see the rest of the code, so you would prob have said that earlier.) But thanks for not just giving a comment but actually some code to look at. – Alfa Bravo Dec 01 '16 at 14:43
I will go ahead and update my answer for posterity. Thanks for the feedback! Some libraries have a cursor commit as syntactic sugar that just calls the connection commit. Some don't. Best to be explicit for the case. – Steve D. Dec 01 '16 at 14:45
Would you know if it is possible to do this method when you do not know how many columns there are. Right now I know that there is a "id", headline", "datestamp", "link" column so I can call them specifically, but what if you only know the column you want to remove, and not all the other columns. How will you put those names in the .format(etc)? – Alfa Bravo Dec 01 '16 at 14:55
@Johan For sqlite3, I suspect not because of the very limited alter table functionality. You can however get information about a table using the .schema command as laid out in this question about [how to describe tables in sqlite3](http://stackoverflow.com/questions/3330435/is-there-an-sqlite-equivalent-to-mysqls-describe-table) – Steve D. Dec 01 '16 at 15:06

sqlite3 Python delete column

1 Answers1