Problem with sqlite

[aiwarrior]

class db:
def __init__(self): #constructor
conn = sqlite3.connect('./db.db')
conn.isolation_level = None
self.cursor = conn.cursor()
try:
self.cursor.execute("CREATE TABLE database
(album,filepath)" )
except:
pass

def add_entry( self, eone , etwo ): #Add entry to database
self.cursor.execute( "INSERT INTO database (album,filepath)
VALUES (?,?)", ( eone , etwo ) )
return 1 #TODO: exception handler

def get_mediadb( self, print_db = False ):
self.cursor.execute( 'SELECT * FROM database' )
if (print_db == True):
print self.cursor.fetchall()

def get_value( self, column ):
self.cursor.execute( "SELECT %s FROM database" % column )
for n in self.cursor:
print n

def destructor(self):
self.cursor.close()

def walking_and_filling(db):
pass


if __name__ == "__main__":
db = db()
#walking_and_filling( db )
for root, dirs, files in os.walk( '''foo_path/''',
topdown=False ):
for name in files:
joined = os.path.join(root, name)
if (name[-3:] == 'mp3' and os.path.isfile( joined ) ):
try:
audio = MP3 (joined, ID3=EasyID3 )
print (audio['album'])
db.add_entry( joined, audio['album'] )
except:
pass
db.get_mediadb( print_db=True )


When i execute this the database doesn't get filled with anything and
the program stays running in memory for ever.
The print statement is just a Unicode string that would get to the
database if i stated it in the add_entry function as a constant
string. It's a really weird problem that i dont seem to understand why
it's working out this way.

Thanks in advance

 

[Duncan Booth]

When i execute this the database doesn't get filled with anything and
the program stays running in memory for ever.

That's odd, when I execute the code you posted I just get "NameError:
global name 'sqlite3' is not defined". You should always try to post the
complete actual code you are using, not just some random part of it.

The print statement is just a Unicode string that would get to the
database if i stated it in the add_entry function as a constant
string. It's a really weird problem that i dont seem to understand why
it's working out this way.

Are you absolutely certain of that? If you use print to try to debug the
value of an object you should usually use repr:


print repr(audio['album'])

What MP3 library are you using?

You have forced a commit after every insert. This slows down sqlite A LOT.
I get about 400 insertions each minute with your code (just inserting fixed
strings) but if I remove the conn.isolation_level assignment and add a
single commit at the end I get in excessive of 20000 per second. Are you
sure you just didn't wait long enough?

 

[Gerhard Häring]

Ok, I'll review your code.

class db:
def __init__(self): #constructor
conn = sqlite3.connect('./db.db')
conn.isolation_level = None

Autocommit mode is mostly for newbies who forget to call commit.
Unfortunately, newbiews find enough other ways to shoot themselves in
the foot. So, in retrospect, maybe I should not have added that feature
to pysqlite ;-)

self.cursor = conn.cursor()
try:
self.cursor.execute("CREATE TABLE database
(album,filepath)" )
except:
pass

try: except: pass without catching *specific* exceptions is generally a
very bad idea. If you're doing something stupid, or another error
happens than the one you want to ignore, you will never know this way.

def add_entry( self, eone , etwo ): #Add entry to database
self.cursor.execute( "INSERT INTO database (album,filepath)
VALUES (?,?)", ( eone , etwo ) )
return 1 #TODO: exception handler

def get_mediadb( self, print_db = False ):
self.cursor.execute( 'SELECT * FROM database' )
if (print_db == True):
print self.cursor.fetchall()

The if clause can be written as just "if print_db:".

def get_value( self, column ):
self.cursor.execute( "SELECT %s FROM database" % column )
for n in self.cursor:
print n

def destructor(self):
self.cursor.close()

Just FYI, Python's "destructor" method is called "__del__", not
"destructor".

def walking_and_filling(db):
pass


if __name__ == "__main__":
db = db()
#walking_and_filling( db )
for root, dirs, files in os.walk( '''foo_path/''',
topdown=False ):
for name in files:
joined = os.path.join(root, name)
if (name[-3:] == 'mp3' and os.path.isfile( joined ) ):
try:
audio = MP3 (joined, ID3=EasyID3 )
print (audio['album'])
db.add_entry( joined, audio['album'] )
except:
pass

Now, this try: except: pass is most probably hiding the real error That
leads to the insert failing. Because you just ignored any errors, you
will never now what exactly went wrong here.

db.get_mediadb( print_db=True )


When i execute this the database doesn't get filled with anything and
the program stays running in memory for ever. [...]

HTH,

-- Gerhard

 

[aiwarrior]

Ok, I'll review your code.

class db:
def __init__(self): #constructor
conn = sqlite3.connect('./db.db')
conn.isolation_level = None

Autocommit mode is mostly for newbies who forget to call commit.
Unfortunately, newbiews find enough other ways to shoot themselves in
the foot. So, in retrospect, maybe I should not have added that feature
to pysqlite ;-)

self.cursor = conn.cursor()
try:
self.cursor.execute("CREATE TABLE database
(album,filepath)" )
except:
pass

try: except: pass without catching *specific* exceptions is generally a
very bad idea. If you're doing something stupid, or another error
happens than the one you want to ignore, you will never know this way.

def add_entry( self, eone , etwo ): #Add entry to database
self.cursor.execute( "INSERT INTO database (album,filepath)
VALUES (?,?)", ( eone , etwo ) )
return 1 #TODO: exception handler

def get_mediadb( self, print_db = False ):
self.cursor.execute( 'SELECT * FROM database' )
if (print_db == True):
print self.cursor.fetchall()

The if clause can be written as just "if print_db:".

def get_value( self, column ):
self.cursor.execute( "SELECT %s FROM database" % column )
for n in self.cursor:
print n

def destructor(self):
self.cursor.close()

Just FYI, Python's "destructor" method is called "__del__", not
"destructor".

def walking_and_filling(db):
pass

if __name__ == "__main__":
db = db()
#walking_and_filling( db )
for root, dirs, files in os.walk( '''foo_path/''',
topdown=False ):
for name in files:
joined = os.path.join(root, name)
if (name[-3:] == 'mp3' and os.path.isfile( joined ) ):
try:
audio = MP3 (joined, ID3=EasyID3 )
print (audio['album'])
db.add_entry( joined, audio['album'] )
except:
pass

Now, this try: except: pass is most probably hiding the real error That
leads to the insert failing. Because you just ignored any errors, you
will never now what exactly went wrong here.

db.get_mediadb( print_db=True )

When i execute this the database doesn't get filled with anything and
the program stays running in memory for ever. [...]

HTH,

-- Gerhard

I'm sorry about not saying showing the libraries. It was not on
purpose.

import os
import sqlite3
from mutagen.easyid3 import EasyID3
from mutagen.mp3 import MP3


##def tree(path):
## node = ()
## for node in os.listdir( path ):
## if( os.path.isdir( path + node )):
## tree(path+node)
## return path

class db:
def __init__(self): #constructor
conn = sqlite3.connect( './db.db' )
conn.isolation_level = None
self.cursor = conn.cursor()
try:
self.cursor.execute( "CREATE TABLE database
(album,filepath)" )
except:
pass

def add_entry( self, eone , etwo ): #Add entry to database
self.cursor.execute( "INSERT INTO database (album,filepath)
VALUES (?,?)", ( eone , etwo ) )
return 1 #TODO: exception handler

def get_mediadb( self, print_db = False ):
self.cursor.execute( 'SELECT * FROM database' )
if (print_db == True):
print self.cursor.fetchall()

def get_value( self, column ):
self.cursor.execute( "SELECT %s FROM database" % column )
for n in self.cursor:
print n

def destructor( self ):
self.cursor.close()

def walking_and_filling( db ):
pass


if __name__ == "__main__":
db = db()
#walking_and_filling( db )
for root, dirs, files in os.walk( '''foo_path/''',
topdown=False ):
for name in files:
joined = os.path.join(root, name)
if (name[-3:] == 'mp3' and os.path.isfile( joined ) ):
try:
audio = MP3 (joined, ID3=EasyID3 )
print (audio['album'])
db.add_entry( joined, audio['album'] )
except:
pass
db.get_mediadb( print_db=True )

This is all the code. Some of that try pass code is just something i
glued to create a clean slate database file

 

[Tzury Bar Yochay]

after executing insert
do conection.commit()

 

[Tim Roberts]

I'm sorry about not saying showing the libraries. It was not on
purpose.

You didn't make any comment on the rest of Gerhard's suggestion, nor does
it appear that you took any action to correct them.

You should get out of the habit of using extra parentheses in "if" and
"print" statements. They are not needed in Python, and they make the code
more difficult to read.

self.cursor.execute( "CREATE TABLE database
(album,filepath)" )

Note the order of the fields: album, then path.

def add_entry( self, eone , etwo ): #Add entry to database
self.cursor.execute( "INSERT INTO database (album,filepath)
VALUES (?,?)", ( eone , etwo ) )
return 1 #TODO: exception handler

Again note the order of the fields here: album, then path.

def get_value( self, column ):
self.cursor.execute( "SELECT %s FROM database" % column )
for n in self.cursor:
print n

I suspect you wanted "self.cursor.fetchall()" there, but since you don't
call it, it doesn't matter yet.

db.add_entry( joined, audio['album'] )

Now note the order that you supply the fields: path first, album second.
You are inserting the fields in the wrong order here.

This is all the code. Some of that try pass code is just something i
glued to create a clean slate database file

And such gluing is a very bad idea, because it is apparently hiding the
real cause of your problems. Get rid of the try/except/pass sequences
until you understand what is failing.

 

[Carsten Haese]

I suspect you wanted "self.cursor.fetchall()" there, but since you don't
call it, it doesn't matter yet.

sqlite3's cursor objects support the iteration protocol, so the for-
loop does work without calling fetchall().

 

[aiwarrior]

Ok regarding Gerhard's comment of the try, except, pass, i came to
understand that it's really bad code. And i should have referred that
i put that there be cause i was getting:

Traceback (most recent call last):
File "C:\Python25\Projects\cp.py", line 48, in <module>
db = db()
File "C:\Python25\Projects\cp.py", line 19, in __init__
self.cursor.execute( "CREATE TABLE database (album,filepath)" )
OperationalError: table database already exists


But when i tried to handle the Operational error with :

try:
self.cursor.execute( "CREATE TABLE database
(album,filepath)" )
except OperationalError:
pass

I would got:
NameError: global name 'OperationalError' is not defined

I searched the Internet and found that sqlite3.OperationalError was
missing. Dumb me.

Now even though i've been reading a bit about exceptions in python
tutorial i've come to realize that my approach won't give me the
results i want.
To my understanding the try statement will still execute the statement
within it until it produces the error. So even if i caught this
OperationalError i wouldn't know what to do with it.

What i'm going to study is whether it's possible to evaluate if a
table already exists, and if so act accordingly.

Are you absolutely certain of that? If you use print to try to debug the
value of an object you should usually use repr
print repr(audio['album'])

As Gerhard correctly guessed i'm a newbie and didn't know of the
existence repr. I've been reading about it in python documentation but
have yet to analyze it more carefully.

I guess the parentheses are just some form of maniac stupidity. Will
try to be more clean about them.

Thanks for all the patience and hope i've been more forthcoming in
this post.

 

[aiwarrior]

Ok regarding Gerhard's comment of the try, except, pass, i came to
understand that it's really bad code. And i should have referred that
i put that there be cause i was getting:

Traceback (most recent call last):
File "C:\Python25\Projects\cp.py", line 48, in <module>
db = db()
File "C:\Python25\Projects\cp.py", line 19, in __init__
self.cursor.execute( "CREATE TABLE database (album,filepath)" )
OperationalError: table database already exists


But when i tried to handle the Operational error with :

try:
self.cursor.execute( "CREATE TABLE database
(album,filepath)" )
except OperationalError:
pass

I would got:
NameError: global name 'OperationalError' is not defined

I searched the Internet and found that sqlite3.OperationalError was
missing. Dumb me.

Now even though i've been reading a bit about exceptions in python
tutorial i've come to realize that my approach won't give me the
results i want.
To my understanding the try statement will still execute the statement
within it until it produces the error. So even if i caught this
OperationalError i wouldn't know what to do with it.

What i'm going to study is whether it's possible to evaluate if a
table already exists, and if so act accordingly.

Are you absolutely certain of that? If you use print to try to debug the
value of an object you should usually use repr
print repr(audio['album'])

As Gerhard correctly guessed i'm a newbie and didn't know of the
existence repr. I've been reading about it in python documentation but
have yet to analyze it more carefully.

I guess the parentheses are just some form of maniac stupidity. Will
try to be more clean about them.

Thanks for all the patience and hope i've been more forthcoming in
this post.

 

[Gerhard Häring]

[...]
What i'm going to study is whether it's possible to evaluate if a
table already exists, and if so act accordingly. [...]

You can use a statement like "CREATE TABLE IF NOT EXISTS tbl(col1, col2);".

If you just want to check, you can query SQLite's schema metadata with
something like

def table_exists(con, table_name):
return con.execute("select count(*) from sqlite_master where
type='table' and tbl_name=?", (table_name,)).fetchone()[0]

-- Gerhard