ProgrammingError: (1064

[Juris Program]

Hello,

Ultimately I want to update the value of the field ENTRY as shown:

ENTRY='New text ' here \ '

Essentially I want any characters the user types to be put into the record, and not interpreted by python, MySQL, etc.  So if the user enters \\ it will be \\ in the database, and so forth.

At the end of this message is the error I'm getting when I try to run this so far.  How can I achieve this goal of getting this text into MySQL without confusing any modules with special characters along the way? Any help would be greatly appriciated.

[Dbg]>>> q
"UPDATE journal SET ENTRY_NUM=15,DATETIME_ENTERED='2004-09-17 00:23:40',TITLE='test title',ENTRY='New text ' here \\',PURPOSE=null,DATAPATH=null,PRIVACY_LEVEL=null,LOCATION=null,COMMENTS=null WHERE ENTRY_NUM=15;"
>>> Unhandled exception while debugging...
Traceback (most recent call last):
  File "C:\Python23\Lib\site-packages\MySQLdb\cursors.py", line 170, in __do_query
    db.query(q)
ProgrammingError: (1064, "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'here \\',PURPOSE=null,DATAPATH=null,PRIVACY_LEVEL=null,LOCATION=")
[Dbg]>>>

[Juris Program]

I wrote a more concise version of this question in response to http://sourceforge.net/forum/forum.php?thread_id=1075920&forum_id=70461

So probably best to focus on answering that one.

Thanks,

Greg

[Andy Dustman]

You don't show any code, so it's impossible to answer your question precisely. However, it is very likely that you are inserting your query values directly into the SQL statement, i.e. you are doing something like this:

base_query = """UPDATE journal SET ENTRY_NUM=%d,DATETIME_ENTERED='%s',
TITLE='%s',ENTRY='%s',PURPOSE=%s,DATAPATH=%s,
PRIVACY_LEVEL=%s,LOCATION=%s,COMMENTS=%s
WHERE ENTRY_NUM=%d;"
query = base_query % (values)
c.execute(query)

This is wrong because it doesn't quote things properly. You need to do this:

base_query = """UPDATE journal SET ENTRY_NUM=%s,DATETIME_ENTERED=%s,
TITLE=%s,ENTRY=%s, PURPOSE=%s, DATAPATH=%s,
PRIVACY_LEVEL=%s,LOCATION=%s,COMMENTS=%s
WHERE ENTRY_NUM=%s"
c.execute(query, values)

Note carefully: For each value, use %s for the value placeholder, even for numeric values and NULL; and do not put %s in quotes, i.e. '%s'. Values should be a tuple, and None is converted to NULL automatically.

Alternately, you can also use %(column_name)s and then values would be a dictionary in which the keys are column_name. Use a unique value (per query) for column_name; it does not have to be an actual column name.

Additionally, do not include a semi-colon at the end of your SQL statement. This will upset MySQL. You can't submit multiple statements through a single call of execute().

[Juris Program]

Thanks for the help. I used the %s thing and everything works great now.

You mentioned not using semi-colons on the SQL statements and I tried removing them. However I noticed that my function below doesn't work when it does not end with a semi-colon. Any ideas why that would be? Is the \n a problem? Thanks, Greg

def CreateSQL(self):
    TableName=self.components['txtTableName'].text
    TempValues=[]
    for RowName in self._widgets:
        TempValues.append([])
        TempValues[-1].append(self.components[RowName[1]].text)
        TempValues[-1].append(self.components[RowName[2]].checked)
        TempValues[-1].append(self.components[RowName[3]].text)
        TempValues[-1].append(self.components[RowName[4]].checked)
        TempValues[-1].append(self.components[RowName[5]].checked)
        TempValues[-1].append(self.components[RowName[6]].checked)
        TempValues[-1].append(self.components[RowName[7]].text)
        TempValues[-1].append(self.components[RowName[8]].stringSelection)
        TempValues[-1].append(self.components[RowName[9]].text)
    #create_definition:
    #col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
    #   [PRIMARY KEY]  [COMMENT 'string'] [reference_definition]
    #
    create_definitions=''
    for row in TempValues:
        create_definitions+=',' + row[8]+self.FormType(row[7],row[6])+self.FormNull(row[5])+self.FormDefault(row[2])+self.FormAutoIncrement(row[3])+self.FormPrimaryKey(row[4])+self.FormComment(row[0])+'\n'
    create_definitions=create_definitions[1:] #remove comma
    stmt="CREATE TABLE IF NOT EXISTS " + TableName + " (" + "\n" + create_definitions + ");"
    print stmt
    try:
        self.parent._database._cursor.execute(stmt)
    except:
        result=self.parent._database._cursor.fetchall()
        print result
        #raise
        return False
    else:
        result=self.parent._database._cursor.fetchall()
        print result
        return True

[Juris Program]

whoah, the tabs didn't hold! sorry.

If you can't read my function, this is the value in variable stmt right before self.parent._database._cursor.execute(stmt)
So basically it only works with the semicolon at the end.

CREATE TABLE IF NOT EXISTS testtable1 (
var1 TINYINT(2) NOT NULL AUTO_INCREMENT PRIMARY KEY
,var2 TINYINT(2) NULL
,var3 TINYINT(2) NULL
);

[Juris Program]

Ok, I answered my own question. It seems to be caused by the newline character. Guess you can't do that.

(Should have tried that before asking.... sorry)

Greg