no multi-row query with _mysql

[ciccio]

Hi.
This code always give "error 1064 you have an error in your sql sintax..."

import _mysql
db=_mysql.connect( host='212.239.25.107', db='x', user='y', passwd='z' )
q = '''
INSERT INTO sessions ( IDCustomer ) VALUES ( 8 ) ;
INSERT INTO sessions ( IDCustomer ) VALUES ( 9 ) ;
'''
db.query( q )

If I cut and paste the query in heidiSQL it works.
This python code work only if I put only one insert in the query.
It must be something banal but I can't understand. I tried "db.autocommit" True and False.
I would like to query with many rows and to put my own BEGIN and COMMIT in the sql strings, and also use sql variables, this is why I used _mysql instead of MySQLdb.

I use: last mysql-python1.2.2, activestate python 2.5.4, win xp sp3.

Thanks!

[ciccio]

Thanks for the answer !

I have also found this:
The default "query" func accept by default only one line query, because of mysql c api.
To use multi-line query you have to pass one flag to the connect.

import _mysql
import MySQLdb.constants.CLIENT as CLIENT
db=_mysql.connect( ......, client_flag=CLIENT.MULTI_STATEMENTS )

this way it works, but it takes more lines of code to get the results or even see if the queries went ok
( you have to use a loop with "db.next_result()" ).

[Kyle VanderBeek]

The use of a semicolon (;) is a purely command line/interactive interface convention. It is not valid in a programmatic connection, and will give you this error (correctly).

Ideally, you'd use parameters too.

sql = "INSERT INTO sessions (IDCustomer) VALUES (%s)"

c = db.cursor()
c.execute(sql, (8,))
c.execute(sql, (9,))

Essentially, everywhere you'd put a semicolon in an interactive session, you use a separate execute() call.

There is also executemany() and the multi-value MySQL-specific syntax for some types of queries.