Gadfly: single-quote in varchar?

[Andy Todd]

Sam Penrose <spenrose at intersight.com> wrote in 
news:mailman.1022089949.8091.python-list at python.org:

> I cannot get Gadfly to INSERT a string containing a single-quote (') 
> into a VARCHAR field. Gadfly appears to insist that strings passed to 
> its parsing framework be delimited with single quotes:
> 
> >>> cursor.execute("INSERT INTO t (Description) VALUES ('alpha')") NOT
> >>> cursor.execute('INSERT INTO t (Description) VALUES ("alpha")') 
> 
> which may or may not be connected, (and rules out the obvious 'INSERT 
> INTO t (Description) VALUES ("alpha's before beta")') but at any rate no 
> variation on
> 
> >>> cursor.execute("INSERT INTO t (Description) VALUES ('alpha\'s before 
> >>> beta')") cursor.execute("INSERT INTO t (Description) VALUES
> >>> ('alpha\\'s  before beta')") cursor.execute("INSERT INTO t
> >>> (Description) VALUES ('alpha\\\'s  before beta')") 
> 
> seems to work, although some fail silently, neither raising an error or 
> inserting the row. Description has been defined as a VARCHAR field and 
> happily accepts strings with other punctuation, including double-quotes. 
> Anyone know if this is a Gadfly constraint, or notice some dunderheaded 
> mistake I'm making?
> 
> Gadfly 1.0.0rc1, Unix python 2.2 on Mac OS X.
> 
> TIA,
> Sam
> 
> 
> 

Your problem is because you are not using parameters. These will make your 
life much easier when dealing with database modules. First, a little gentle 
RTFM;

The DB-API 2.0 - http://www.python.org/topics/database/DatabaseAPI-2.0.html 
- see the section on 'paramstyle'. Although that is a little formal, have a 
look at a couple of sections of the Gadfly documentation - 
http://gadfly.sourceforge.net/gadfly.html#dynamic-values - Shows you how to 
use parameters in your SQL and why they are good thing (performance), and 
this section - http://gadfly.sourceforge.net/gadfly.html#table-creation-
and-data-types - explains why data types are a bit of a moot point in 
Gadfly.

So, if you re-write your statement as;

>>> cursor.execute("INSERT INTO t (Description) VALUES (?)", ("alpha's", ))

Everything should be sweet. Note that the argument after the comma should 
be a tuple, even if it is only one element. Also note that you can put 
*anything* in you tuple values, strings, numbers, in fact any kind of 
Python object and Gadfly will store it for you.

HTH,
Andy
-- 
Contents free posts a speciality