1,105,633 Community Members

wxPython and sqlite3 request

Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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. :)
******************************************************************************

Member Avatar
J-M DESMETTRE
Newbie Poster
11 posts since May 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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)
Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Member Avatar
J-M DESMETTRE
Newbie Poster
11 posts since May 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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)
Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
woooee
Posting Maven
2,798 posts since Dec 2006
Reputation Points: 783 [?]
Q&As Helped to Solve: 836 [?]
Skill Endorsements: 12 [?]
 
0
 

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.
Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
woooee
Posting Maven
2,798 posts since Dec 2006
Reputation Points: 783 [?]
Q&As Helped to Solve: 836 [?]
Skill Endorsements: 12 [?]
 
0
 

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.

Member Avatar
woooee
Posting Maven
2,798 posts since Dec 2006
Reputation Points: 783 [?]
Q&As Helped to Solve: 836 [?]
Skill Endorsements: 12 [?]
 
0
 

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
Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
pyTony
pyMod
6,104 posts since Apr 2010
Reputation Points: 818 [?]
Q&As Helped to Solve: 1,056 [?]
Skill Endorsements: 42 [?]
Moderator
Featured
 
0
 

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.

Member Avatar
richieking
Posting Shark
926 posts since Jun 2009
Reputation Points: 40 [?]
Q&As Helped to Solve: 172 [?]
Skill Endorsements: 3 [?]
 
0
 

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

Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
stefh
Light Poster
47 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
pyTony
pyMod
6,104 posts since Apr 2010
Reputation Points: 818 [?]
Q&As Helped to Solve: 1,056 [?]
Skill Endorsements: 42 [?]
Moderator
Featured
 
0
 

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: