0

I am saving locations on a web page to an sqlite3 database using python/selenium and sqlite3. Some of the locations contain a single quote

For example maryville, John's lane, London

I know that when creating a data base locally, I must use two single quotes to escape this. John''s lane. REF:- questions/603572/how-to-properly-escape-a-single-quote-for-a-sqlite-database How can this be achieved when scraping a website.

My Code is below:-

# get locations
locs = browser.find_elements_by_class_name("meta")
for loc in locs:
    if loc.text !="":
        print loc.text
        query += ",\'"+loc.text.replace(', ','-')+"\'"

I get this error because of the presence of the 's 

cur.execute("INSERT INTO LOCATIONS VALUES("+query+");")
sqlite3.OperationalError: near "s": syntax error

I am saving the full address to one fields. Thanks in advance for your help.

Newbe
  • 127
  • 1
  • 4
  • 14
  • 1
    Don't escape and catenate values to SQL, use placeholders: http://stackoverflow.com/a/1310001/2681632. There are enough SQL injections around already. – Ilja Everilä Mar 29 '16 at 11:39

1 Answers1

3

You should use placeholders instead of manually trying to escape your data.

conn = sqlite3.connect(':memory:')
conn.execute('create table locations (name text)')
locs = list(map("{}'s".format, range(100)))
conn.execute('insert into locations values ({})'.format(
    '), ('.join(['?'] * len(locs))  # Build your placeholders
), locs)
print(list(conn.execute('select * from locations limit 5')))

will print

[("0's",), ("1's",), ("2's",), ("3's",), ("4's",)]

A question mark in the query to execute signifies a placeholder and your DB-API (sqlite3 in this case) will handle replacing those with the data you provide. It'll also handle required escaping.

Also you should consider using executemany, since manually building a huge placeholder list for VALUES (?), (?), (?), ... will cause

sqlite3.OperationalError: too many terms in compound SELECT

so do

conn.executemany('insert into locations values (?)', ((x,) for x in locs))

and you can insert thousands of rows.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127