[Tutor] help with data insert into Access table

[Peter Otten]

Ahmed, Shakir wrote:

> I am trying to insert a record in the access table, the value has a quote
> and could not insert the record. Any idea how I can insert records like
> this quotes.

> cursor.execute("INSERT INTO PicsPostInfo(Pics_name) values ('Site Name's
> Harbor.JPG')") Traceback (most recent call last):
>   File "<interactive input>", line 1, in <module>
> ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access
> Driver] Syntax error (missing operator) in query expression ''Site Name's
> Harbor.JPG')'. (-3100) (SQLExecDirectW)")

Every compliant database module has a paramstyle attribute, e. g. for 
sqlite3:

>>> import sqlite3
>>> sqlite3.paramstyle
'qmark'

"qmark" means that you use "?" instead of the actual value.
http://www.python.org/dev/peps/pep-0249/ has a list of available 
`paramstyle`s.

Assuming that the database driver you are using uses "qmark" your code would 
become

cursor.execute("INSERT INTO PicsPostInfo(Pics_name) VALUES (?)",
               ("Site Name's Harbor.JPG",))

i. e. in addition to the SQL statement there is a tuple (in this case a 1-
tuple, the trailing comma is necessary!) holding the values. This way is the 
only reasonable way to go when the actual data is provided by your users 
because it prevents SQL injection attacks.

See also http://xkcd.com/327/