MariaDB, pypyodbc, "Unknown prepared statement handler" executing "SELECT" query on table loaded with "LOAD DATA LOCAL INFILE."
Question: Python 3.4.3, MariaDB 10.0.21, MariaDB ODBC Connector 1.0.0, pypyodbc 1.3.3,
all 64-bit on 64-bit Windows 7.

I've got a python script that's supposed to create a table, populate it with
data from a fixed-width file, and then run a SELECT statement against it. All
simple stuff. My script looks something like this:

    
    
    import pypyodbc
    
    def do_stuff(name, password, filepath):
        db = pypyodbc.connect(driver = "{MariaDB ODBC 1.0 Driver}",
                        server = "localhost", uid = name,
                        pwd = password, autocommit = True)
    
        cursor = db.cursor()
    
        cursor.execute("CREATE TABLE `foo`.`bar` (`col1` INT);")
        cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE `foo`.`bar` (@row) SET col1 = SUBSTR(@row,1,1)" % filepath.replace("\\", "\\\\"))
        for row in cursor.execute("SELECT * FROM `foo`.`bar`"):
            print(row)
        db.close()
    
    do_stuff("root", "password", r"C:\\Users\\laj\\Desktop\\test.txt")
    

It grabs the first character from each line in the the text file and sticks it
in the sole column in the table. When the "SELECT" statement comes around,
however, I get hit with the following error:

    
    
    Traceback (most recent call last):
      File "test.py", line 25, in <module>
        do_stuff("root", "oag123", r"C:\\Users\\laj\\Desktop\\test.txt")
      File "test.py", line 21, in do_stuff
        for row in cursor.execute("SELECT * FROM `foo`.`bar`"):
      File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 1605, in execute
      File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 1631, in execdirect
      File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 986, in check_success
      File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 964, in ctrl_err
    pypyodbc.Error: ('HY000', '[HY000] Unknown prepared statement handler (5) given to mysqld_stmt_reset')
    

What really gets me, though is that I can get rid of the error simply by
closing and reopening the database connection in between populating the table
and executing the "SELECT," like so:

    
    
    import pypyodbc
    
    def do_stuff(name, password, filepath):
        db = pypyodbc.connect(driver = "{MariaDB ODBC 1.0 Driver}",
                        server = "localhost", uid = name,
                        pwd = password, autocommit = True)
    
        cursor = db.cursor()
    
        cursor.execute("CREATE TABLE `foo`.`bar` (`col1` INT);")
        cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE `foo`.`bar` (@row) SET col1 = SUBSTR(@row,1,1)" % filepath.replace("\\", "\\\\"))
    
        db.close()
        db = pypyodbc.connect(driver = "{MariaDB ODBC 1.0 Driver}",
                        server = "localhost", uid = name,
                        pwd = password, autocommit = True)
    
        cursor = db.cursor()
    
    
        for row in cursor.execute("SELECT * FROM `foo`.`bar`"):
            print(row)
        db.close()
    
    do_stuff("root", "password", r"C:\\Users\\laj\\Desktop\\test.txt")
    

Unfortunately, this isn't actually a valid solution to my problem. Not only is
it something I shouldn't have to do, but it also doesn't help when it comes to
temporary tables because they just get dropped during the disconnect phase of
that "fix." Any insight would be great, this is driving me up a wall.


Answer: `execute` does not return what you think:

    
    
    cursor.execute("SELECT ...");
    rows = cur.fetchall();
    for row in rows ...
    

