wxPython and sqlite3 request

[stefh]

Hi there,

my name is Stéphane, i'm french and a beginner with Python...

I've registered on a french forum about Python to ask a question regarding an issue i have with a code, but it seems the members of this forum are not active.

I paste the message i posted on the forum (in french (below is the english translation))

******************************************************************************
Bonjour à tous!

Je me suis inscrit sur votre site en espérant trouver de l'aide car je débute avec Python...

Je m'intéresse depuis pas mal de temps à ce langage mais ce n'est qu'assez récemment que j'ai décidé de m'accrocher dans ma découverte et mon apprentissage (non professionnel, simplement personnel) de Python.
A côté de ça j'utilise beaucoup Php (et bien évidemment html, css, javascript); aussi je pense peut-être m'intéresser au développement de site avec Python... Mais je n'en suis pas encore là! :)
Et dans l'immédiat je fais face à quelques difficultés pour lesquelles j'aimerais bien avoir votre avis et vos conseils:

je travail sur une petite application (en python donc, plus wxpython) qui utilise sqlite, j'ai créé une classe pour la connection et les traitements à effectuer sur ma base de données, or j'ai un message d'erreur lors de l'exécution d'une méthode de ma classe que je n'arrive pas à m'expliquer.
Le voici:

c.execute("select titre, code, date from codes where id = " + idrow)
sqlite3.OperationalError: near " ": syntax error

Je ne comprend pas cette erreur, et je ne vois pas ce que dois faire pour corriger ce problème... Je ne comprends à quoi fait référence l'espace du 'near " "', de plus ma requête me renvoie bien le résultat attendu.
J'ai essayé cette même requête dans une console sqlite, en fournissant moi-même la valeur du champ id et je n'ai aucun message d'erreur.

Donc je pense que l'erreur est liée à l'utilisation de cette variable mais je ne vois pas comment faire autrement pour la passer dans la chaine de ma requête.

D'avance merci pour votre accueil et votre aide! Bonne journée à tous. :)
******************************************************************************

Translation:

******************************************************************************
Hello everyone!

I registered on your site hoping to find help because I started with Python ...

I'm interested for quite some time to this language but it is only fairly recently that I decided to hang in my discovery and my learning (non-professional, staff only) of Python.
Beside that, I use a lot PHP (and of course html, css, javascript), so I think I could be interested in sites development with Python ... But I'm not yet to this point! :)
For now I am facing some difficulties which I would appreciate your opinions and advice:

I work on a small application (thus Python, + wxpython) that uses sqlite, I created a class for the connection and perform treatments on my database, yet I get an error when executing of a method in my class that I can not explain.
Here the message:

c.execute ("select title, code, date codes from WHERE id =" + idrow)
sqlite3.OperationalError: near " ": syntax error

I do not understand this error, and I do not know what do to fix this problem ... I do not understand what refers to the space of ' near " " ', nonetheless my query returns the expected outcome.
I tried the same query in sqlite console, myself i give the value of the id field, and I have no error message.

So I think the error is related to the use of this variable but I do not know how else to pass it into my query string.

In advance thank you for your hospitality and your help! Good day everyone. :)
******************************************************************************

[J-M DESMETTRE]

Hi there,

my name is Stéphane, i'm french and a beginner with Python...

I've registered on a french forum about Python to ask a question regarding an issue i have with a code, but it seems the members of this forum are not active.

I paste the message i posted on the forum (in french (below is the english translation))

******************************************************************************
Bonjour à tous!

Je me suis inscrit sur votre site en espérant trouver de l'aide car je débute avec Python...

Je m'intéresse depuis pas mal de temps à ce langage mais ce n'est qu'assez récemment que j'ai décidé de m'accrocher dans ma découverte et mon apprentissage (non professionnel, simplement personnel) de Python.
A côté de ça j'utilise beaucoup Php (et bien évidemment html, css, javascript); aussi je pense peut-être m'intéresser au développement de site avec Python... Mais je n'en suis pas encore là! :)
Et dans l'immédiat je fais face à quelques difficultés pour lesquelles j'aimerais bien avoir votre avis et vos conseils:

je travail sur une petite application (en python donc, plus wxpython) qui utilise sqlite, j'ai créé une classe pour la connection et les traitements à effectuer sur ma base de données, or j'ai un message d'erreur lors de l'exécution d'une méthode de ma classe que je n'arrive pas à m'expliquer.
Le voici:

c.execute("select titre, code, date from codes where id = " + idrow)
sqlite3.OperationalError: near " ": syntax error

Je ne comprend pas cette erreur, et je ne vois pas ce que dois faire pour corriger ce problème... Je ne comprends à quoi fait référence l'espace du 'near " "', de plus ma requête me renvoie bien le résultat attendu.
J'ai essayé cette même requête dans une console sqlite, en fournissant moi-même la valeur du champ id et je n'ai aucun message d'erreur.

Donc je pense que l'erreur est liée à l'utilisation de cette variable mais je ne vois pas comment faire autrement pour la passer dans la chaine de ma requête.

D'avance merci pour votre accueil et votre aide! Bonne journée à tous. :)
******************************************************************************

Translation:

******************************************************************************
Hello everyone!

I registered on your site hoping to find help because I started with Python ...

I'm interested for quite some time to this language but it is only fairly recently that I decided to hang in my discovery and my learning (non-professional, staff only) of Python.
Beside that, I use a lot PHP (and of course html, css, javascript), so I think I could be interested in sites development with Python ... But I'm not yet to this point! :)
For now I am facing some difficulties which I would appreciate your opinions and advice:

I work on a small application (thus Python, + wxpython) that uses sqlite, I created a class for the connection and perform treatments on my database, yet I get an error when executing of a method in my class that I can not explain.
Here the message:

c.execute ("select title, code, date codes from WHERE id =" + idrow)
sqlite3.OperationalError: near " ": syntax error

I do not understand this error, and I do not know what do to fix this problem ... I do not understand what refers to the space of ' near " " ', nonetheless my query returns the expected outcome.
I tried the same query in sqlite console, myself i give the value of the id field, and I have no error message.

So I think the error is related to the use of this variable but I do not know how else to pass it into my query string.

In advance thank you for your hospitality and your help! Good day everyone. :)
******************************************************************************

Hi Stéphane,

Try this :

c.execute("SELECT titre, code, date FROM codes WHERE id = %s" % idrow)

[stefh]

Hi, thanks for the answer!

Yes i've thought about what you propose, and i've tried it but i have the same error:

c.execute("select titre, code, date from codes where id = %s" % idrow)
sqlite3.OperationalError: near " ": syntax error

[J-M DESMETTRE]

Hi there,

my name is Stéphane, i'm french and a beginner with Python...

I've registered on a french forum about Python to ask a question regarding an issue i have with a code, but it seems the members of this forum are not active.

I paste the message i posted on the forum (in french (below is the english translation))

******************************************************************************
Bonjour à tous!

Je me suis inscrit sur votre site en espérant trouver de l'aide car je débute avec Python...

Je m'intéresse depuis pas mal de temps à ce langage mais ce n'est qu'assez récemment que j'ai décidé de m'accrocher dans ma découverte et mon apprentissage (non professionnel, simplement personnel) de Python.
A côté de ça j'utilise beaucoup Php (et bien évidemment html, css, javascript); aussi je pense peut-être m'intéresser au développement de site avec Python... Mais je n'en suis pas encore là! :)
Et dans l'immédiat je fais face à quelques difficultés pour lesquelles j'aimerais bien avoir votre avis et vos conseils:

je travail sur une petite application (en python donc, plus wxpython) qui utilise sqlite, j'ai créé une classe pour la connection et les traitements à effectuer sur ma base de données, or j'ai un message d'erreur lors de l'exécution d'une méthode de ma classe que je n'arrive pas à m'expliquer.
Le voici:

c.execute("select titre, code, date from codes where id = " + idrow)
sqlite3.OperationalError: near " ": syntax error

Je ne comprend pas cette erreur, et je ne vois pas ce que dois faire pour corriger ce problème... Je ne comprends à quoi fait référence l'espace du 'near " "', de plus ma requête me renvoie bien le résultat attendu.
J'ai essayé cette même requête dans une console sqlite, en fournissant moi-même la valeur du champ id et je n'ai aucun message d'erreur.

Donc je pense que l'erreur est liée à l'utilisation de cette variable mais je ne vois pas comment faire autrement pour la passer dans la chaine de ma requête.

D'avance merci pour votre accueil et votre aide! Bonne journée à tous. :)
******************************************************************************

Translation:

******************************************************************************
Hello everyone!

I registered on your site hoping to find help because I started with Python ...

I'm interested for quite some time to this language but it is only fairly recently that I decided to hang in my discovery and my learning (non-professional, staff only) of Python.
Beside that, I use a lot PHP (and of course html, css, javascript), so I think I could be interested in sites development with Python ... But I'm not yet to this point! :)
For now I am facing some difficulties which I would appreciate your opinions and advice:

I work on a small application (thus Python, + wxpython) that uses sqlite, I created a class for the connection and perform treatments on my database, yet I get an error when executing of a method in my class that I can not explain.
Here the message:

c.execute ("select title, code, date codes from WHERE id =" + idrow)
sqlite3.OperationalError: near " ": syntax error

I do not understand this error, and I do not know what do to fix this problem ... I do not understand what refers to the space of ' near " " ', nonetheless my query returns the expected outcome.
I tried the same query in sqlite console, myself i give the value of the id field, and I have no error message.

So I think the error is related to the use of this variable but I do not know how else to pass it into my query string.

In advance thank you for your hospitality and your help! Good day everyone. :)
******************************************************************************

Hi again,

Try this one :

c.execute('SELECT titre, code, date FROM codes WHERE id = "%s" ' % idrow)

[stefh]

Thanks J-M

Does not work as expected!

The string, returned by my function after the query, appears a very short instant in the TxtCtrl where i put it, but then disappear... :S

And there's no error message...

Wonder why...

[stefh]

In another function of my class i do this:

c.execute("delete from codes where id = " + idrow)

The same error message is given!

c.execute("delete from codes where id = " + idrow)
sqlite3.OperationalError: near " ": syntax error

And it deletes the current row and all the rows with an id < idrow (but not the rows whose id > idrow).

Additionally, when I test this query in the sqlite console, only the row == idrow is deleted.

So i think it confirms that the problem is in the query formulation, regarding the use of the variable idrow.

I am disappointed, i do not know how to solve this problem.

[woooee]

You should use a dictionary for all queries, deletes, etc. %s opens the DB to SQL injection.

c.execute("select titre, code, date from codes where id=:d_key", {"d_key":idrow})
x=cur.fetchall()
#
# multiple select fields
c.execute("select titre, code, date from codes where id=:d_key and col=:d_col", {"d_key":idrow, "d_col":column_var})
#
c.execute("delete from codes where id=:d_key", {"d_key":idrow})
con.commit())
#
#etc.

[stefh]

Hi woooee,

i didn't know about the use of dictionnary in this context... I'm going to try this way.

And i take note about the sql injections risk (however in this case it's actually only a desktop application...)

[stefh]

Well, i'v tried the proposition of woooee:

c.execute("select titre, code, date from codes where id=:d_key", {"d_key":idrow})

There's a strange issue:

i make a string from the result of the query and put it into a TxtCtrl, the value of the TxtCtrl receives indeed the string but then it is immediatly empty! :S

Can't understand why...

I show you the code of the function i use, so that you may help me to solve my problems:

def readRow(self, idrow):
		
    c = self.conn.cursor()
		
    c.execute("select titre, code, date from codes where id=:d_key", {"d_key":idrow})
		
    getRow = ""
		
    for ligne in c:
		
        getRow = "// " + ligne["titre"] + " " + ligne["date"] + "\n\n" + ligne["code"];
		
    c.close()
		
    return getRow

Can you also tell me what to do instead the "for ligne in c" to use the values of the array returned by the query (the query is on only one row of the table)?

Thanks for your help! :)

[woooee]

You have to fetch the records, fetchall or fetchone. Fetchall returns a list of tuples, fetchone returns a tuple.

c.execute("select titre, code, date from codes where id=:d_key", {"d_key":idrow})
found = c.fetchall()
print "type found", type(found)
print "len", len(found)
for rec in found:
    print rec
    for field in rec:
        print "     ", field

Post back with a simple, complete program if this doesn't work as SQLite is pretty straight forward.

[woooee]

Note also that you should not be creating a new cursor every time this function is executed.

class SQLTest:
    def __init__(self):
        self.con = sqlite.connect(table_name )
        self.cur = self.con.cursor()
   
        ##--- ONLY CREATE FIRST TIME
        self.cur.execute("CREATE TABLE IF NOT EXISTS...etc"

        read_value = self.read_row(idrow)

    def read_row(self, idrow):
        self.cur.execute("select titre, code, date from codes where id=:d_key", {"d_key":idrow})
        found = self.cur.fetchall()
        if found is None:
            print "result is None=no recs found"
        else:
            print "type found", type(found)
            print "len", len(found)
            for rec in found:
                print rec
                for field in rec:
                    print "     ", field 
        return whatever

[stefh]

Thank for the answer woooee! :)

I've tried, like that:

def readRow(self, idrow):
		
    c = self.conn.cursor()
		
    c.execute("select titre, code, date from codes where id=:d_key", {"d_key":idrow})
        
    ligne= c.fetchone()
			
    c.close()
			
    getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
			
    return getRow

It works! But i have this error message:

getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
TypeError: 'NoneType' object is unsubscriptable

What do i have to do now...? :S

Ps: i take note about the cursor

[stefh]

It seems that the problem is upstream, when I send my data to my function...

In the first file i have:

def OnListboxread(self, e):
		
    temp = e.GetString()
    getidrow = temp.split(":", 1)[0]
    res = self.dbUse.readRow(getidrow)
    self.ain.SetValue(res)

In the other file where is readRow(), i have:

def readRow(self, idrow):
		
    self.c.execute("select titre, code, date from codes where id = :d_key", {"d_key":idrow})
		
    ligne= self.c.fetchone()
			
    self.c.close()
			
    getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
			
    return getRow

The message error is:

Traceback (most recent call last):
  File "/home/steph/scripts/phpy/main.py", line 186, in OnListboxread
    res = self.dbUse.readRow(getidrow)
  File "/home/steph/scripts/phpy/bdd.py", line 37, in readRow
    getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
TypeError: 'NoneType' object is unsubscriptable

But i have no error if in OnListboxread() i do like that:

res = self.dbUse.readRow(48)

No error if i give 48 (for example) as argument to readRow instead of the getidrow var that comes from the split use.

So i think the problem is with the way i find the value of getidrow var...

What can i do with this var, how can i treat it so that there is no more error?

[TrustyTony]

Use prints to console to debug. You can put condition

if ligne:

or

if ligne is not None:

I usualy get this error when I forget that append does not return value in Python but has side effects.

[richieking]

under normal python way of sqlite select.... This should do.
The easier way.

c.execute('select titre, code, date from codes where id=?',idrow )

That is it :)

[stefh]

Yes tonyjv, the query string is what i want, and the result is what is expected...

I'm lost, i don't understand anything no more.

[stefh]

under normal python way of sqlite select.... This should do.
The easier way.

c.execute('select titre, code, date from codes where id=?',idrow )

That is it :)

Thanks for your answer richieking! :)

If i do that way i have this error:

self.c.execute('select titre, code, date from codes where id=?', idrow)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.

And if i do:

self.c.execute('select titre, code, date from codes where id=?', (idrow,))

I have the same error as before:

Traceback (most recent call last):
File "/home/steph/scripts/phpy/main.py", line 186, in OnListboxread
res = self.dbUse.readRow(getidrow)
File "/home/steph/scripts/phpy/bdd.py", line 37, in readRow
getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
TypeError: 'NoneType' object is unsubscriptable

[stefh]

There is another curious thing:

i've tried a print of the value of GetString in the OnListboxread() methode, it prints this:

46: An item 24/02/2011 21:57

51: Another item 25/02/2011 11:21

In this example i've clicked on the listbox item corresponding to 51:...

In all cases GetString has two lines: the line corresponding to 46 (the first item of the listbox) and the line corresponding to the item i clicked.

I can't understand this...

Here is the code of the methode:

def OnListboxread(self, e):
    
    temp = e.GetString()
    getidrow = temp.split(":", 1)[0]
    res = self.dbUse.readRow(getidrow)
    self.ain.SetValue(res)
    self.aout.SetValue("Sauvegarde ouverte")
    self.SetStatusText("")
    
    self.refwin.Close(True)
    
    print temp

I feel lost.. :/

[stefh]

Edit:

I've solved this issue by setting a default selected item in the listbox...

Despite of this i've still have an error when querying a single row:
Traceback (most recent call last):
File "/home/steph/scripts/phpy/main.py", line 187, in OnListboxread
res = self.dbUse.readRow(getidrow)
File "/home/steph/scripts/phpy/bdd.py", line 37, in readRow
getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
TypeError: 'NoneType' object is unsubscriptable

[TrustyTony]

Print those lines right after setting ligne. Is it really ligne or self.ligne? In every case value is None.

[stefh]

This what i'm doing...

def readRow(self, idrow):

    self.c.execute("select titre, code, date from codes where id = :d_key", {"d_key" : idrow})

    ligne = self.c.fetchone()

    getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]

    return getRow

[TrustyTony]

Maybe the idraw value is not exactly correct. Print repr of all variables. For white space issue strip() method is sexy ;)

[stefh]

Clearly the issue comes from the idrow.

If i replace idrow by a value i define there is no error, for example like that:

self.c.execute("select titre, code, date from codes where id = 50")

The idrow comes from this method:

def OnListboxread(self, e):
    
    temp = e.GetString()
    getidrow = temp.split(":", 1)[0]
    res = self.dbUse.readRow(getidrow) #There i send idrow that is use in c.execute of readRow() methode
    self.ain.SetValue(res)
    
    self.refwin.Close(True)

[TrustyTony]

and value of temp and getidrow before calling... print, print, print...

BTW I can see from your signature why you are learning the readable Python, but it still takes effort (docstrings, comments, understandable variable names) even in Python.

[stefh]

Hi tonyjv,

thanks for you answer :)

I've just tried this:

def OnListboxread(self, e):

    self.refwin.Close(True)
    temp = e.GetString()
    getidrow = temp.split(":", 1)[0]
    
    print temp
    print getidrow
    
    res = self.dbUse.readRow(getidrow)
    self.ain.SetValue(res)
    self.aout.SetValue("Sauvegarde ouverte")
    self.SetStatusText("")

And it outputs that:

52: String test 25/02/2011 12:34

52

Traceback (most recent call last):
File "/home/steph/scripts/phpy/main.py", line 191, in OnListboxread
res = self.dbUse.readRow(getidrow)
File "/home/steph/scripts/phpy/bdd.py", line 37, in readRow
getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
TypeError: 'NoneType' object is unsubscriptable

Is this helpful to find the solution?

Ps: why do you say that you can see from my signature why i'm learning the readable Python? I don't understand... :)
And what the "readable Python" means?

[woooee]

Traceback (most recent call last):
File "/home/steph/scripts/phpy/main.py", line 191, in OnListboxread
res = self.dbUse.readRow(getidrow)
File "/home/steph/scripts/phpy/bdd.py", line 37, in readRow
getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
TypeError: 'NoneType' object is unsubscriptable

No record was found. You have to test for "None". See below.

Clearly the issue comes from the idrow.

If i replace idrow by a value i define there is no error, for example like that:
Python Syntax (Toggle Plain Text)
self.c.execute("select titre, code, date from codes where id = 50")

"50" is a string in this case. Is the variable an integer? You didn't post the DB definition so we have no idea what type of object is in the DB.

def read_row(self, idrow):

        ##----- converting to string
        self.cur.execute("select titre, code, date from codes where id=:d_key", {"d_key":str(idrow)})
        found = self.cur.fetchone()
        if found is None:
            print "result is None=no recs found"
        else:
            for field in found:
                print "     ", field 
        return whatever

[stefh]

Hi,

Tested "print type(idrow)" and it says <type 'unicode'>

Here the schema of my table:

id : integer primary key autoincrement
titre: varchar
code: varchar
date: varchar

[woooee]

Tested "print type(idrow)" and it says <type 'unicode'>

Here the schema of my table:

id : integer primary key autoincrement

So idrow has to be converted to an integer. Hopefully you can see why. Also, are you using Python 3.X?

try:
    idrow_int = int(idrow)
    self.cur.execute("select titre, code, date from codes where id=:d_key", {"d_key":idrow_int})
    ## etc.
except:
    print idrow, "can not be converted to an integer"

[TrustyTony]

I mean: When you write properly Python according the language philosophy 'code is read more often than written' you also know what you have written before, not only God (like with writing PHP, Perl or J code)

[stefh]

I forget to say:
The current query i use returns the records i requested!

self.c.execute("select titre, code, date from codes where id = :rowid", {"rowid" : idrow})

However it says:

Traceback (most recent call last):
File "/home/steph/scripts/phpy/main.py", line 187, in OnListboxread
res = self.dbUse.readRow(getidrow)
File "/home/steph/scripts/phpy/bdd.py", line 37, in readRow
getRow = "// " + ligne[0] + " " + ligne[2] + "\n\n" + ligne[1]
TypeError: 'NoneType' object is unsubscriptable

So where does this error come from?

Another thing:

you say " "50" is a string in this case. "

I thought it was an integer for sqlite because there's no quotes around it...

A string value should not be written like that:
'select titre, code, date from codes where id = "50"'

And an integer like that:
'select titre, code, date from codes where id = 50'

??