CODEWITHSUNDEEP
python-3.xmariadb
EvilTeach
EvilTeach

Reputation: 28872

Python 3.7.5 Mariadb 10.4.11 query binding causes exception

In this contrived example I am trying to select 'Fred' hard coded, then with a bind variable.

The hard select works fine. The bind variable example fails with an exception.

import sys
import string
import mysql.connector

def sanity_test():
    try:
        print("connecting")
        connection = mysql.connector.connect(user='root', password='root',
                                             host='127.0.0.1', database="MyJournal")
        print("test1")
        cursor1 = connection.cursor()
        cursor1.execute("select 'Fred'")
        records = cursor1.fetchall()
        print("There are " + str(cursor1.rowcount) + " rows in the first result set")

        print("test2")
        cursor2 = connection.cursor()
        name = "Fred";
        cursor2.execute("select %s", name)
        records2 = cursor2.fetchall()
        print("There are " + str(cursor2.rowcount) + " rows in the second result set")

    except:
        e = sys.exc_info()[0]
        print ("Exception: ", e)
    else:
        cursor1.close()
        cursor2.close()
        connection.close()
    input("Press Enter to continue...")

if __name__ == '__main__':
    sanity_test()

The resulting output is

connecting
test1
There are 1 rows in the first result set
test2
Exception:  <class 'mysql.connector.errors.ProgrammingError'>

What am I missing? What techniques are available to get more information than the terse exception?

Upvotes: 0

Views: 61

Answers (1)

Georg Richter
Georg Richter

Reputation: 7496

1) Handle exceptions for each logical block (e.g. connect, then cursor.create + execute), but not over several blocks.

2) Get more descriptive error message

except mysql.connector.Error as err:
    print("Unexpected error : ", format(err))

3) SQL standard requires single or double quotes for strings within statements, e.g. cursor.execute("SELECT '%s'", ("foo",))

Upvotes: 1

Related Questions