pyodbc and mySQL

Question

I am unable to connect to mySQl db using pyodbc.

Here is a snippet of my script:

import pyodbc
import csv

cnxn = pyodbc.connect("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;DATABASE=mydb; UID=root; PASSWORD=thatwouldbetelling;") 
crsr = cnxn.cursor()

with open('C:\\skunkworks\\archive\\data\\myfile.csv','r') as myfile:
    rows = csv.reader(myfile, delimiter=',', quotechar='"')
    for row in rows:
        insert_str = 'INSERT into raw_data VALUES(something, something)'
        print insert_str
        #crsr.execute(insert_str)
    cnxn.commit()
    myfile.close()

I get this error at the pyodbc.connect() line:

pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnectW)')

I have another question regarding this error (and Python scripts in general). When I run this as a script, it fails silently (I was expecting a stack trace). I have to type each line in manually to find where the error occured.

I am being a bit lazy for now (no exception handling) - is this normal behaviour of a Python script without exception handling to fail silently?

[Edit]

I am not using mysqldb because I am already using pyodbc to extract my data from another source (MS Access). Ok, not a good reason - but I am already grappling with pyodbc and I dont really fancy having to wrestle with another library/module/package(whatever its called in Python) for a "one off" job. I just want to move my data of from various data sources in the Windows environment to mySQl on Linux. once on Linux, I'll be back on terra firma.

That is the entire 'script' right there. I just saved the code above into a file with a .py extension, and I run python myscript.py at the command line. I am running this on my XP machine

Answer 1

MySQLdb (or oursql) and pyodbc both have the same interface (DB-API 2), only you don't have to deal with ODBC's issues if you use the former. I strongly recommend you consider using MySQLdb (or oursql) instead.

Answer 2

First, According to the official docs, if you want to connect without creating a DSN, you need to specify OPTION=3 in the connection string:

ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=venu;PASSWORD=venu;OPTION=3;"

If that fails to work, I'd further troubleshoot by creating a DSN.

Second, no Python should not be failing silently. If that is the case when you run your script, there is something else amiss.