Johan Greyling Johan Greyling - 4 days ago 5
Python Question

sqlite3 Python delete column

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

Answer

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.

Comments