MySQLdb Columns as variables

[Ismatus3]

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") .

[Gribouillis]

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

print( repr(curso1._last_executed) )

to see which actual query was run.

[Ismatus3]

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'

[Ismatus3]

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

[Gribouillis]

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.

[Ismatus3]

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)`

[Gribouillis]

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.

[Ismatus3]

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 .

[Gribouillis]

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))

[Ismatus3]

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 ..

[Gribouillis]

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.

[Ismatus3]

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);

[Gribouillis]

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 ?

[Ismatus3]

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 | 

[Ismatus3]

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

[Ismatus3]

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 .

[Gribouillis]

Can we discuss the matter in your shoutbox ?