[Tutor] help with data insert into Access table

[Ahmed, Shakir]

Thanks, it worked exactly what I was trying to do so.

-----Original Message-----
From: Tutor [mailto:tutor-bounces+shahmed=sfwmd.gov at python.org] On Behalf Of Peter Otten
Sent: Wednesday, January 29, 2014 11:47 AM
To: tutor at python.org
Subject: Re: [Tutor] help with data insert into Access table

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/





We value your opinion. Please take a few minutes to share your comments on the service you received from the District by clicking on this link<http://my.sfwmd.gov/portal/page/portal/pg_grp_surveysystem/survey%20ext?pid=1653>.