Python try except sqlite

Using try except in python sqlite3

Question: I want to execute executemany and get exception or suceeded status for each row I want to get error for each row Something like: in one variable after executemany is over. In that block, there is no error handling because it IS error handling.

Try/Except not working when fetching from SQLite database

I am bulding an app that extracts different attributes from an XML file in my iTunes library export. Since not every song has a genre, finding one per song will not always work. When the program inserts a genre into the ‘Genre’, it creates an automatic id

CREATE TABLE Genre ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT UNIQUE ); 

The thing is, when there is no ‘Genre’ found in the XML file, nothing is inserted, meaning I cannot later fetch the id created by the addition of something to put it in a tabke in order to do relational queries. To stop this, I put the insert and fetch inside a try/except that would catch the

TypeError: ‘NoneType’ object is not subscriptable

error when it is unable to

. In the except I established the genre as «No Genre» to generate the unique id for the case that there were no genre found. This, even though it catches an exception in the try block, isnt running. Error:

TypeError Traceback (most recent call last) ~\Documents\python\tracks.py in 83 cur.execute('SELECT id FROM Artist WHERE name = ? ', (genre, )) ---> 84 genre_id = cur.fetchone()[0] 85 except: TypeError: 'NoneType' object is not subscriptable During handling of the above exception, another exception occurred: TypeError Traceback (most recent call last) ~\Documents\python\tracks.py in 87 cur.execute('''INSERT OR IGNORE INTO Genre (name) VALUES ( ? )''', ( genre, ) ) 88 cur.execute('SELECT id FROM Artist WHERE name = ? ', (genre, )) ---> 89 genre_id = cur.fetchone()[0] 90 91 TypeError: 'NoneType' object is not subscriptable 

Help! Why isn’t the try/except not working?

 try: cur.execute('''INSERT OR IGNORE INTO Genre (name) VALUES ( ? )''', ( genre, ) ) cur.execute('SELECT id FROM Artist WHERE name = ? ', (genre, )) genre_id = cur.fetchone()[0] except: genre = "No Genre" cur.execute('''INSERT OR IGNORE INTO Genre (name) VALUES ( ? )''', ( genre, ) ) cur.execute('SELECT id FROM Artist WHERE name = ? ', (genre, )) genre_id = cur.fetchone()[0] 

Okay, solution first

import sqlite3 conn = sqlite3.connect('systeminfo.db') cur = conn.cursor() genre = 'test' try: cur.execute('''INSERT OR IGNORE INTO Genre (name) VALUES ( ? )''', ( genre, ) ) cur.execute('SELECT id FROM Artist WHERE name = ? ', (genre, )) genre_id = cur.fetchone() if genre_id is None: print('Select did not find any artist for <> genre'.format(genre)) else: print('Select resulted in Artist ID <>'.format(genre_id[0])) except Exception as e: print('Exception: <>'.format(e)) raise Exception(e) conn.commit() conn.close() 

See, there’s a possibility that cur.fetchone() can result in a row or None if there is no row. So, let’s do an if..then to check for None.

Here’s what seems to be happening. Scroll way at the bottom of the post to find your answer.

CREATE TABLE Artist ( id integer not null primary key autoincrement, name text, genre text ); CREATE TABLE Genre ( id integer not null primary key autoincrement, name text unique ); 

sqlite> select * from Artist; Run Time: real 0.000 user 0.000086 sys 0.000054 sqlite> select * from Genre; Run Time: real 0.000 user 0.000092 sys 0.000064 sqlite> 

import sqlite3 conn = sqlite3.connect('systeminfo.db') cur = conn.cursor() genre = 'test' try: cur.execute('''INSERT OR IGNORE INTO Genre (name) VALUES ( ? )''', ( genre, ) ) cur.execute('SELECT id FROM Artist WHERE name = ? ', (genre, )) genre_id = cur.fetchone()[0] except: cur.execute('''INSERT OR IGNORE INTO Genre (name) VALUES ( ? )''', ( genre, ) ) cur.execute('SELECT id FROM Artist WHERE name = ? ', (genre, )) genre_id = cur.fetchone()[0] conn.commit() conn.close() 

Your errors are happening in 2 places. In your try block, there’s an error at genre_id = cur.fetchone()[0] . Once the error is hit, the control goes to except block. In that block, the code is repeated. That means, the error is repeated. In that block, there is no error handling because it IS error handling. So, python throws another error in except block for the same thing genre_id = cur.fetchone()[0] .

Clean up the issue

import sqlite3 conn = sqlite3.connect('systeminfo.db') cur = conn.cursor() genre = 'test' try: cur.execute('''INSERT OR IGNORE INTO Genre (name) VALUES ( ? )''', ( genre, ) ) cur.execute('SELECT id FROM Artist WHERE name = ? ', (genre, )) genre_id = cur.fetchone()[0] except Exception as e: print('Exception: <>'.format(e)) conn.commit() conn.close() 

Alright. In except we want to handle the exception and perhaps print out the exception. So, we will use except Exception as e . Now, error information is in e . We print that out and that’s it.

$ python myfile.py Exception: 'NoneType' object is not subscriptable 

But what if I want to show where the error is?

Add raise Exception(e) right under print(‘Exception: <>‘.format(e)) . So, the result becomes this:

$ python myfile.py Exception: 'NoneType' object is not subscriptable Traceback (most recent call last): File "testy.py", line 9, in genre_id = cur.fetchone()[0] TypeError: 'NoneType' object is not subscriptable During handling of the above exception, another exception occurred: Traceback (most recent call last): File "testy.py", line 12, in raise Exception(e) Exception: 'NoneType' object is not subscriptable 

That raise statement lists out on the screen where issue happened.

Using sqlite3 in python, trying to SELECT using a, I am trying to search for 133769 substring in our SQL database where the actual no value is 133769-006. I can’t seem to find the syntax to use the «IN» line in SQL. My current code return

Raising exception during SQLite database connection in Python

import sqlite3 database = "sample.db" def dbConnection(database): try: connection = sqlite3.connect(database) db_cursor = connection.cursor() db_cursor.execute("show tables;") rows = db_cursor.fetchall() for row in rows: print row connection.close() except sqlite3.Error, e: print "Error in connection",e dbConnection("enb.db") 

It is raising this exception:-

Error in connection near "show": syntax error 

I can’t see anything wrong with the syntax as I just want to view the tables in the database. What could be the problem here?Thanks

«SHOW TABLES» is not supported by SQLite. It is valid for other databases such as MySQL.

How to ‘show tables’ in SQLite

SQLite doesn’t support «show» functions. You can use SELECT name FROM sqlite_master where type=’table’ . I found it from this thread, Android sqlite show tables

Exception — python try, except an error from modules, When a module class is not in my script but used by one of the modules (I did imported explicitly) how do I catch his error? For example: from sqlite3 import dbapi2 as sqlite class sqldb: def

Exception when trying to use an user-defined function in sqlite3 using python

I’m using Python 2.6.4 and its module sqlite3 for a small database project and I have the following problem: I’m trying to use a user-defined function, I mean, a function that you define in Python to use after inside your queries. The function is a wrapper for other function I have in another module. The problem is that when doing the query, I always get an AttributeError exception with the message: ‘builtin_function_or_method’ object has no attribute ‘execute’ and I have no clue why. The code is the following. Could you point me what I’m doing wrong?

Wrapper function:

def SQLAreSimilar(userSelectedName, artistName): ''' Wrapper to the areSimilar function of strUtils to use it directly inside the queries. Not to be called directly. ''' if strUtils.areSimilar(userSelectedName, artistName): return 1 else: return 0 

Function that actually does the query. Note the use of «create_function» method of the Connection object.

def getArtistsBySimilarName(name): ''' Returns all artists with a similar name, according to the Levenshtein distance. The DB is supposed to be initialised. Returns a dictionary of dictionaries with the data of all similar artists indexed by its CODARTIST, the PK. The dictionary is empty if no row is returned. None is returned on error. ''' try: con = sqlite3.connect(genericConf.SQL_DBNAME) con.row_factory = sqlite3.Row con.create_function("ARESIMILAR", 2, SQLAreSimilar) cur = con.cursor rows = cur.execute(specificConf.SQL_SELECT_ARTIST_SIMILARNAME, name) retDict = <> for row in rows: d = <> d['NUMCD'] = row['NUMCD'] d['NAME'] = row['NAME'] retDict[row['CODARTIST']] = d return retDict except: return None 

And finally, the query . It is inside the module called «specificConf». So it is used correctly in the function above, the problem is not there.

SQL_SELECT_ARTIST_SIMILARNAME = u''' SELECT CODARTIST, NAME, NUMCD, ARESIMILAR(?, NAME) AS SIMILAR FROM ARTIST WHERE SIMILAR = 1 ''' 

cur = con.cursor # sets `cur` to a method 

cur = con.cursor() # calls the method and sets `cur` to the return value 

This is why you were getting the error saying cur has no execute attribute:

AttributeError exception with the message: ‘builtin_function_or_method’ object has no attribute ‘execute’

Python — How to catch sqlite3.IntegrityError with, python sqlite pytest. Share. Improve this question. Follow edited Dec 30, 2020 at 14:16. Chris. 116k 84 So just remove the try-except from your method and only keep the cursor.execute statement (or create a new one without it and use it in your test methods) or instead of handling the exception inside your func’s …

Python sqlite3 executemany except for each row

I want to execute executemany and get exception or suceeded status for each row

data = [('H19150', 9677, 519.0, 558.0),#success ('345', 9666, 606.4, 651.8),# primary key exception ('H19159', 9665, 657.4, 705.0),#unique key exception ('H19215', 9678, 528.4, 569.4),#success ('6546', 324, 528.4, 569.4),#foreign key exception ('H19158', 45, 528.4, 569.4)]#success import sqlite3 try: conn.executemany('INSERT or IGNORE INTO coated VALUES (. )', data) except #catch error for each row: #what to write here 

I want to get error for each row Something like:

success primary key exception unique key exception success foreign key exception success 

in one variable after executemany is over.

Is it possible to do this?

As far as I know, if you use executemany() , then the entire insert will behave as a single succeed/fail transaction. If you want to get feedback on each tuple, then you should iterate your list and do a separate insert for each tuple:

for tuple in data: try: conn.execute('INSERT or IGNORE INTO coated VALUES (?)', tuple) except #catch error for each row: 

Python SQLite: database is locked, I’m presuming you are actually using sqlite3 even though your code says otherwise. Here are some things to check: That you don’t have a hung process sitting on the file (unix: $ fuser cache.db should say nothing) There isn’t a cache.db-journal file in the directory with cache.db; this would indicate a crashed session that hasn’t …

Источник

Обработка исключений#

Посмотрим на пример использования метода execute при возникновении ошибки.

В таблице switch поле mac должно быть уникальным. И, если попытаться записать пересекающийся MAC-адрес, возникнет ошибка:

In [37]: con = sqlite3.connect('sw_inventory2.db') In [38]: query = "INSERT into switch values ('0000.AAAA.DDDD', 'sw7', 'Cisco 2960', 'London, Green Str')" In [39]: con.execute(query) ------------------------------------------------------------ IntegrityError Traceback (most recent call last) ipython-input-56-ad34d83a8a84> in module>() ----> 1 con.execute(query) IntegrityError: UNIQUE constraint failed: switch.mac 

Соответственно, можно перехватить исключение:

In [40]: try: . : con.execute(query) . : except sqlite3.IntegrityError as e: . : print("Error occurred: ", e) . : Error occurred: UNIQUE constraint failed: switch.mac 

Обратите внимание, что надо перехватывать исключение sqlite3.IntegrityError, а не IntegrityError.

Источник