Valid SQL?

[Harlin Seritt]

I have this string that I am sending via a Cursor.execute() using
MySQLdb:

insert into table Ping82_eb13__elearn__ihost__com (`dateTime`,
`values`) values(
"Fri May 12 11:39:02 2006", "1")

Does anyone see anything wrong with this SQL syntax?

Thanks,

Harlin Seritt

[Diez B. Roggisch]

Harlin Seritt wrote:

I have this string that I am sending via a Cursor.execute() using
MySQLdb:

insert into table Ping82_eb13__elearn__ihost__com (`dateTime`,
`values`) values(
"Fri May 12 11:39:02 2006", "1")

Does anyone see anything wrong with this SQL syntax?

How about a stacktrace?

http://www.catb.org/~esr/faqs/smart-questions.html

And you should use the parametrized version of execute, because then the
DB-API will take care of escaping the strings properly for you. Which is
the problem here, btw: you are using "" for strings, where SQl requires ''.

Diez

[Tim Chase]

> I have this string that I am sending via a Cursor.execute() using

MySQLdb:

insert into table Ping82_eb13__elearn__ihost__com (`dateTime`,
`values`) values(
"Fri May 12 11:39:02 2006", "1")

Does anyone see anything wrong with this SQL syntax?

While this is the *python* list, rather than a SQL list...

It looks like you're using two diff. styles of quoting. And
using back-quotes at that. IIRC, ANSI-SQL (nebulous standard as
it is, implemented to taste by each vendor) calls for using
single-quotes as strings. Some RDBMS engines support the
double-quote (MySQL does). None that I know of support the
back-tick. Unless it's an RDBMS scheme for surrounding column or
table-names that might have spaces in them (or might be SQL
keywords). You might also want to make sure that your RDBMS
doesn't have a column data-type of "datetime" (MySQL does) which
might choke matters too...having a column-name that potentially
clashes with the name of a datatype is just asking for trouble :)

Additionally, the syntax for INSERT INTO statements usually
leaves the word TABLE as optional. I think this is the first
time I've seen someone opt for it :) Most SQL I've seen just does

INSERT INTO tblFoo (field1, field2) VALUES ('value1', 'value2')

You don't include the DDL that defines the structure of the table
into which you're shoving matters, so it's somewhat hard to tell
what's going on. Are primary keys being violated? Are
data-types awry?

Lastly, you don't include the text of the error message that
you're getting back...most error messages try to be helpful, and
in this case, it would certainly be helpful. :)

Just a few thoughts,

-tkc

[Harlin Seritt]

Thanks for the help. I set up the SQL statement to be like:
INSERT INTO tblFoo (field1, field2) VALUES ('value1', 'value2')

I get this error:

insert into Web1_DLTDS10_RootSite (dateTime, values) values('Sat Apr 15
08:58:13
2006', '0')
Traceback (most recent call last):
File "librarian.py", line 45, in ?
Cursor.execute(InsertValuesSQL)
File "C:\Python24\lib\site-packages\MySQLdb\cursors.py", line 137, in
execute
self.errorhandler(self, exc, value)
File "C:\Python24\lib\site-packages\MySQLdb\connections.py", line 33,
in defau
lterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your
SQL syntax
; check the manual that corresponds to your MySQL server version for
the right s
yntax to use near 'values) values('Sat Apr 15 08:58:13 2006', '0')' at
line 1")

Any idea why I'm getting this?

Thanks,

Harlin Seritt

[Harlin Seritt]

I am using the exact same query string generated and it works when i
type it in the MySQL client but doesn't work when using the MySQLdb
module.

:(

[John Salerno]

Harlin Seritt wrote:

I am using the exact same query string generated and it works when i
type it in the MySQL client but doesn't work when using the MySQLdb
module.

I've been messing around with mysqldb lately, and one reason I get your
error message is if I'm not closing parentheses properly.

Can you paste the exact Python code that you are using here, as well as
the MySQL code used in the client window? I assume in the client you use
a semicolon, but in Python you don't?

I don't know if you'd get that particular error if the problem is
something like the values not matching to their type, especially since
you say it works on the client but not in Python.