Hello ,
in a python application i wanted to insert datas to some columns of a table , the columns tha have as name "XS" , "S" , and "M" . i tried this query : curso1.execute('INSERT INTO donnees (%s) VALUES (%s);',(liste1[i],qtetaillevar[i])) , but when it compiles it , when i get this error message :
when liste1[i] = "XS" and qtetaillevar[i] = '4'

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 ''XS') VALUES ('3')' at line 1") .

According to this answer, you could catch the exception and try

print( repr(curso1._last_executed) )

to see which actual query was run.

Hello Gribouillis
Thank you for answering me again , but maybe i'm not searching for that answer . I'm working on the same last function after creating dynamicly entries , I need now to insert data into the database from these dynamic entries . I try to explain to you more : the table in the database have fixed number of columns which are : (column1,column2,...,column7) for example , and i want to insert in some of these columns , that is depending on the elements of liste1 , liste1 can be ["column2","column3","column4"] or ["column5","column7"] , so for this last example (when liste1 = ["column5","column7"]) , i want to insert datas into the table into these columns : (column5,column7) .
I puted print( repr(curso1._last_executed) ) in the function "valider1()" , and i got that error message :

AttributeError: 'Cursor' object has no attribute '_last_executed'

here is the code of the function "valider1()" :

def valider1():
           print "valider1"
           #print liste1
           numbonvar = numbon_entry.get()
           modelidvar = modelid_entry.get()
           connect()
           curso = conn.cursor()
           curso.execute(' select client.*, model.* from client,model where client.clientid=model.client AND modelid=%s ;'%modelidvar)
           row1 = curso.fetchall()
           if row1 :
              liste1 = row1[0][12].split(",")

           print liste1
           listetmp = liste1
           qtetaillevar = listetmp
           print liste1
           print numbonvar
           print modelidvar
           curso.execute('INSERT INTO donnees (numbon,model) VALUES (%s,%s);',(numbonvar,modelidvar))
           curso.close()
           conn.commit()
           numbon_entry.delete(0,END)
           for i in range(len(liste1)):

                qtetaillevar[i] = tailles_entry[i].get()
                print qtetaillevar[i]
                if row1 :
                   liste1 = row1[0][12].split(",")
                print liste1[i]
                connect()
                curso1 = conn.cursor()
                print( repr(curso1._last_executed) )
                curso1.execute('INSERT INTO donnees (%s) VALUES (%s);',(liste1[i],qtetaillevar[i]))

                curso1.close()
                conn.commit()
                tailles_entry[i].delete(0,END)
                 #qtetaillevar[i] = str(qtetaillevar[i])
                 #print "qtetaillevar[%s]"%i

The problem is that I don't use this module MySQLdb. Try to use this function to replace your calls to the .execute() method

def my_execute(cursor, *args, **kwd):
    try:
        cursor.execute(*args, **kwd)
    finally:
        print( repr(cursor._last_executed))

then write my_execute(cursor,'INSERT INTO donnees (%s) VALUES (%s);',(liste1[i],qtetaillevar[i])), etc. You may consider using unicode strings too.

Hello again ,
i tried to use the function you gave me , it printed this :

"INSERT INTO donnees ('XS') VALUES ('4');"

but didn't execute the query , it shows the same error message :

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 ''XS') VALUES ('4')' at line 1") .

I inserted the function like this :
`for i in range(len(liste1)):

            qtetaillevar[i] = tailles_entry[i].get()
            print qtetaillevar[i]
            if row1 :
               liste1 = row1[0][12].split(",")
            print liste1[i]
            connect()
            curso1 = conn.cursor()
            def my_execute(curso1, *args, **kwd):
                try:
                    curso1.execute(*args, **kwd)
                finally:
                    print( repr(curso1._last_executed))
            my_execute(curso1,'INSERT INTO donnees (%s) VALUES (%s);',(liste1[i],qtetaillevar[i]))       
            #curso1.execute('INSERT INTO donnees (%s) VALUES (%s);',(liste1[i],qtetaillevar[i]))
            print( repr(curso1._last_executed) )
            curso1.close()
            conn.commit()
            tailles_entry[i].delete(0,END)`

The message means that the SQL engine thinks that

INSERT INTO donnees ('XS') VALUES ('4');

is not a valid SQL statement for mysql database. Check the SQL syntax.

Apart from that, you should write my_execute() funtion outside your function (at script level).
Also make sure you call my_execute() for all your requests.

Hello Gribouillis
But it's what i wanted to tell to SQL engine :

INSERT INTO donnees (XS) VALUES ('4');

I don't know what to do to have this query , but with my_execute() shows also the same error message , so i keep the simple way to execute the query : curso1.execute('INSERT INTO donnees (%s) VALUES (%s);',(liste1[i],qtetaillevar[i])) , as you said , i just need to write the good syntax .

It is not the same query: you removed the ' around XS. Does the semi colon at the end change anything ? There must be a syntax error somewhere.

Perhaps you could try to access your mysql database with a client like phpmyadmin and issue a query by hand to test the query.

I would stick to my_execute if I were you, because it can be useful to trace SQL queries for other issues which may occur once this one is solved. What you can do is

def my_execute(cursor, *args, **kwd):
    try:
        cursor.execute(*args, **kwd)
    finally:
        pass
        # comment the next line for normal execution (without trace)
        print("SQL: %s" % repr(cursor._last_executed))

Hello Gribouillis

Thank you so much for trying to help me . Sure the problem comes from the bad SQL syntax of the query.
Finaly the query that must be executed must be like this : INSERT INTO donnees (XS) VALUES ('3');
as you said , i don't know from where the "'" came from .
Also , i couldn't use easily your function "my_execute()" , i don't understand it good ..

Your error appears to be similar to this one. You can try

curso1.execute('INSERT INTO donnees (%s) VALUES (%%s);' % liste1[i],(qtetaillevar[i],))

or

my_execute(curso1, 'INSERT INTO donnees (%s) VALUES (%%s);' % liste1[i],(qtetaillevar[i],))

if you want to check the actual query visually. If it does not work, use

my_execute(curso1, "INSERT INTO donnees (%s) VALUES ('%s');" % (liste1[i], qtetaillevar[i],))

What my_execute() does is very simple. It executes your SQL statement, and it prints the actual query that was sent to mysql to stdout, even if the query fails.

Maybe the problem come form that the table "donnees" have a primary key , which must not be NULL , because after adding : print 'INSERT INTO donnees (%s) VALUES (%s);' % (liste1[i],qtetaillevar[i])
it printed good :

INSERT INTO donnees (XS) VALUES (2);

I don't think it has anything to do with the table's content or schema. It's a pure syntax issue. Does it work with the statements I wrote above ?

I tell you here the structure of the database , the is the result of : SHOW CREATE TABLE donnees;

| CREATE TABLE 'donnees' (
'numbon' int(11) NOT NULL,
'XXS' int(11) default NULL,
.
.
.
'XXL' int(11) default NULL,
'model' int(11) NOT NULL,
PRIMARY KEY ('numbon'),
KEY 'model' ('model'),
CONSTRAINT 'donnees_ibfk_1' FOREIGN KEY ('model') REFERENCES 'model' ('modelid')

ENGINE=InnoDB DEFAULT CHARSET=latin1 | 

As you see , in the function "valider1()" there is :
curso.execute(' select client.*, model.* from client,model where client.clientid=model.client AND modelid=%s ;'%modelidvar)

so after , when i want to insert datas into "XXS" or other column , it can not be without inserting a value to "numbon" or "modelid"
WAit me plz try your code , and try to use "UPDATE donnees SET .." , it can work if i put the good syntax

Because the columns "numbon" and "modelid" can not be NULL , it is an other issue maybe
For this i think to use the query : "'UPDATE donnees SET %s = '%s' where numbon = '%s' and modelid = '%s';' ,(numbonvar,modelidvar)" , maybe this query can work , i didn't try it yet .

Can we discuss the matter in your shoutbox ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.