CODEWITHSUNDEEP
pythonsqlite
YetAnotherRCG
YetAnotherRCG

Reputation: 38

One of my sqlite rows contains my column names. How do I select it for deletion

I used python version 3.4.3 with the sqlite3 package.

I made mistake while transferring a load of .txt files into sqlite tables. Some of the .txt files had more than one header line. So somewhere in the resulting sql table there is a row containing column names of that table.

For example if I set up a table like this: 

import sqlite3

con = sqlite3.connect(path to a db)

con.execute('CREATE TABLE A_table (Id PRIMARY KEY,name TEXT,value INTEGER)')

rows = [('Id','name','value'),(1,'Ted',111),(2,'Thelma',22)]

con.executemany('INSERT INTO A_table (Id,name,value) Values(?,?,?)',rows)

If I try to remove the row like this: 

con.execute('DELETE FROM A_table WHERE name = "name"')

It deletes all rows in the table.

In my real database the row that needs to go is not always the first row it could appear at any point. Short of rebuilding the tables what should I do?

I am sure that this has be asked already but I don't have a clue what to call this problem so I have had 0 luck finding help.

Edit: I used python. I am not python.

Upvotes: 2

Views: 456

Answers (2)

CL.
CL.

Reputation: 180230

In SQL, strings use single quotes. Double quotes are used to escape column names, so name = "name" is the same as name = name.

To avoid string formatting problems, it might be a better idea to use parameters:

con.execute("DELETE FROM A_table WHERE name = 'name';")
con.execute("DELETE FROM A_table WHERE name = ?;", ["name"])  # a Python string

Upvotes: 1

svfat
svfat

Reputation: 3373

Use a parametrized query:

con.execute("DELETE FROM A_table WHERE name=?", ('name'))

Upvotes: 2

Related Questions