Python Mysql Execute Error
Contents |
is 'module.Error'. In MySQL it is 'MySQLdb.Error'. Every DB-API python mysqldb operationalerror statement that you execute has the potential to raise an exception. So any time you execute a database query you should surround it
Python Sql Error Handling
with a try/exception block. This includes connections, cursor requests, and statement executions. # Get data from database try: cur.execute("SELECT * FROM song") rows = cur.fetchall() except MySQLdb.Error, e: try: print "MySQL Error [%d]: %s" % (e.args[0], e.args[1]) except IndexError: print "MySQL Error: %s" % str(e) # Print results in comma delimited format for row in rows: for col in row: print "%s," % col print "\n" <-- Home -->
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies
_mysql_exceptions
of this site About Us Learn more about Stack Overflow the company mysqldb execute Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges mysqldb documentation Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: http://www.mikusa.com/python-mysql-docs/exceptions.html Sign up Correct exception handling with python MySQLdb connection up vote 6 down vote favorite 1 I created a small/basic python script to insert data into a MySQL database. I included some error handling - mainly to close the connection and/or prevent hanging connections in the case of an error (...but also to ignore some errors). I thought what I had (see http://stackoverflow.com/questions/21721109/correct-exception-handling-with-python-mysqldb-connection below) was right - it seemed to be working okay. But occasionally I have been getting "Too many connection" errors - which I assumes means I am not actually closing the connection correctly at all (or perhaps error handling isn't right). conn=MySQLdb.connect(host=####, user=####, passwd=####, db=####) curs=conn.cursor() try: curs.execute(sql) conn.commit() except MySQLdb.Error as e: if e[0]!= ###: raise finally: curs.close() conn.close() (I also tried without finally:) The other (I think important) point is that it is that the MySQL database uses an InnoDB storage engine. This is the first time I have used InnoDB engine and perhaps there are some differences to MyISAM that are relevant here, that I am not aware of (like conn.commit(), but for an error).... That seems to be the source of all my other problems! Thanks in advance python error-handling innodb mysql-python share|improve this question edited Feb 14 '14 at 3:34 asked Feb 12 '14 at 7:15 djmac 1591413 You should remove the conn.close() from the try body, as it will always be called within the finally body. Other than that, it looks like you're releasing your connections
implement some mechanism to handle errors gracefully.MySQLdb has MySQLdb.Error exception, a top level exception that can be used to catch all database exception raised by MySQLdb module. from http://thepythonguru.com/handling-errors/ __future__ import print_function import MySQLdb as my try: db = my.connect(host="127.0.0.1", user="root", http://www.kitebird.com/articles/pydbapi.html passwd="", db="world" ) cursor = db.cursor() sql = "select * from city" number_of_rows = cursor.execute(sql) print(number_of_rows) db.close() except my.Error as e: print(e) except : print("Unknown error occurred")123456789101112131415161718192021222324from __future__ import print_functionimport MySQLdb as mytry:db = my.connect(host="127.0.0.1",user="root",passwd="",db="world")cursor = db.cursor()sql = "select * from city"number_of_rows = cursor.execute(sql)print(number_of_rows)db.close()except my.Error as e:print(e)except :print("Unknown error handling error occurred") Two main error in MySQLdbThere are two categories of exception class in MySQLdb you need to be aware:DatabaseError.InterfaceError.DatabaseError: This exception raises when there is problem in data processing, error in sql syntax, mysql internal problems. If a connection is made and a problem arises then DatabaseError will catch it. There are 6 types of DatabaseError:DataError.InternalError.IntegrityError.OperationalError.NotSupportedError.ProgrammingError.DataError: MySQLdb throws this error sql error handling when there is problem in the data processing, like division by zero, numeric value of of range.InternalError: This exception is raised when there is some internal error in MySQL database itself. For e.g invalid cursor, transaction out of sync etc.IntegrityError: This exception is raised when foreign key check fails.OperationalError: This exception is raised for things that are not in control of the programmer. For e.g unexpected disconnect, error in memory allocation etc, selected database not exists.NotSupportedError: This exception is raised when there is method or api that is not supported.ProgrammingError: This exception is raised of programming errors. For e.g table not found, error in mysql syntax, wrong number of parameters specified etc.InterfaceError: When database connection fails for some reason, MySQLdb will raise an InterfaceError. Note InterfaceError only get raise when there is internal problem in connection to the database, MySQLdb will not raise InterfaceError because of wrong database name or password. from __future__ import print_function import MySQLdb as my try: db = my.connect(host="127.0.0.1", user="root", passwd="", db="world" ) cursor = db.cursor() sql = "select * from city" number_of_rows = curso
Script Error Handling Methods for Issuing Statements Portability Notes Resources Appendix Acknowledgment Revision History Python is one of the more popular Open Source programming languages, owing largely to its own native expressiveness as well as to the variety of support modules that are available to extend its capabilities. One of these modules is DB-API, which, as the name implies, provides a database application programming interface. DB-API is designed to be relatively independent of details specific to any given database engine, to help you write database-access scripts that are portable between engines. DB-API's design is similar to that used by Perl and Ruby DBI modules, the PHP PEAR DB class, and the Java JDBC interface: It uses a two-level architecture in which the top level provides an abstract interface that is similar for all supported database engines, and a lower level consisting of drivers for specific engines that handle engine-dependent details. This means, of course, that to use DB-API for writing Python scripts, you must have a driver for your particular database system. For MySQL, DB-API provides database access by means of the MySQLdb driver. This document begins by discussing driver installation (in case you don't have MySQLdb), then moves on to cover how to write DB-API scripts. MySQLdb Installation To write MySQL scripts that use DB-API, Python itself must be installed. That will almost certainly be true if you're using Unix, but is less likely for Windows. Installers for either platform can be found on the Python web site (see the "Resources" section at the end of this document). Verify that your version of Python is 2.3.4 or later, and that the MySQLdb module is installed. You can check both of these requirements by running Python in interactive mode from the command line prompt (something like % for Unix or C:\> for Windows): % python Python 2.4.3 (#1, Aug 29 2006, 14:45:33) [GCC 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0, pie-8.7.9)] on linux2 Type "help", "copyright", "credits" or "license" for more information.