CODEWITHSUNDEEP
pythonsqldatabasesqlite
ROBOTPWNS
ROBOTPWNS

Reputation: 4419

Writing URL into a database (sqlite3.OperationalError: no such column: )

I am trying to write in some URL into a sqlite database. I have gotten this to work without the URL. It even works if I replaces the 'Volumes/data/rc3/2/sdss/SDSS_r_0_0.fits' with a number .

c.execute("INSERT INTO rc3 (ID,ra,dec,radius,PGC_number,new_ra, new_dec, new_radius,ufits,gfits,rfits,ifits,zfits,best,low,in_SDSS_footprint,clean,error)VALUES({},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{})".format(n,pgc,ra,dec,radius,new_ra,new_dec,new_radius,2, 3, 4, 5, 6, 7,8,in_SDSS_footprint,clean,error))

It seems like it is mistakening the URL as a column as it is throwing the error: 

c.execute("CREATE TABLE rc3 (ID INT , PGC_number INT,ra REAL, dec REAL,radius REAL,new_ra REAL,new_dec REAL,new_radius REAL, ufits TEXT, gfits TEXT, rfits TEXT ,ifits TEXT, zfits TEXT, best TEXT, low TEXT,in_SDSS_footprint BIT ,clean BIT, error INT,PRIMARY KEY(ID))")
....
c.execute("INSERT INTO rc3 (ID,ra,dec,radius,PGC_number,new_ra, new_dec, new_radius,ufits,gfits,rfits,ifits,zfits,best,low,in_SDSS_footprint,clean,error)VALUES({},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{})".format(0, 0.0075, 47.2744444444, 0.01999027436515, 2, 0, 0, 0, 'Volumes/data/rc3/2/sdss/SDSS_u_0_0.fits', 'Volumes/data/rc3/2/sdss/SDSS_g_0_0.fits', 'Volumes/data/rc3/2/sdss/SDSS_r_0_0.fits', 'Volumes/data/rc3/2/sdss/SDSS_i_0_0.fits', 'Volumes/data/rc3/2/sdss/SDSS_z_0_0.fits', 'Volumes/data/rc3/2/sdss/SDSS_0_0_BEST.tiff ', 'Volumes/data/rc3/2/sdss/SDSS_0_0_LOW.tiff ', 0, 1, 0))
sqlite3.OperationalError: no such column: Volumes

but I am not sure what to do. Thanks in advance.

Upvotes: 0

Views: 2151

Answers (1)

abarnert
abarnert

Reputation: 365925

Never try to create SQL statements by embedding the values with string formatting commands. Instead, use SQL parameters.

Instead of this:

c.execute("INSERT INTO breakfast (id, spam, eggs) VALUES({}, {}, {})".format(
    id, spam, eggs))

… do this:

c.execute("INSERT INTO breakfast (id, spam, eggs) VALUES(?, ?, ?)",
          id, spam, eggs)

This is explained at the very top of the sqlite3 documentation. But briefly, the reasons to do things this way are (in rough order of importance):

  • Avoids SQL injection if any of the data may come from malicious or incompetent users or external programs.
  • Means you don't have to worry about how to quote/escape strings, format numbers, etc.
  • Makes errors from inappropriate value types clearer and easier to debug.
  • Allows the SQL engine to see your 1000 separate inserts as the exact same statement with different values, instead of 1000 completely different statements, making it more likely it can cache or otherwise optimize.
  • Allows you to use executemany, which can be more readable than a loop, and also may give the SQL engine more optimization opportunities.

In your case, it's the second one you were running into. You're trying to use the string Volumes/data/rc3/2/sdss/SDSS_r_0_0.fits as a value. That's an expression, asking sqlite to divide the Volumes column by the data column, divide that by rc3, etc. If you wanted the string to be stored as a string, you need to put it in quotes.

But, again, don't try to fix this by adding quotes; just use parameters.

Upvotes: 3

Related Questions