How connect to mysql with python via odbc

Question

Im running mysql, pyodbc, python 2.7 loaded on Fedora 14 x64.

Odbcinst.ini is:

# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
#[PostgreSQL]
#Description    = ODBC for PostgreSQL
#Driver     = /usr/lib/psqlodbc.so
#Setup      = /usr/lib/libodbcpsqlS.so
#Driver64   = /usr/lib64/psqlodbc.so
#Setup64    = /usr/lib64/libodbcpsqlS.so
#FileUsage  = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
#Driver     = /usr/lib/libmyodbc5.so
#Setup      = /usr/lib/libodbcmyS.so
Driver64    = /usr/lib64/libmyodbc5.so
Setup64     = /usr/lib64/libodbcmyS.so
FileUsage   = 1

Odbc.ini is :

[MySQL]
Driver = MySQL
Database = mysql
Server = localhost
Socket = /var/lib/mysql/mysql.sock
User = rooter
Password = sshh

Mysql.sock is empty? /var/lib/mysql/mysql.sock has 0.B

python script is:

import pyodbc

#pyodbc.pooling = False 
conn = pyodbc.connect('DRIVER={MySQL};SOCKET=/var/lib/mysql/mysql.sock;UID=rooter;PWD=sshh') 

csr = conn.cursor()
csr.execute("SET GLOBAL event_scheduler = ON")
csr.close()

conn.close() 
del csr

I cant seem to connect with above script, Using isql i get Connected!

MyERROR msg:

Traceback (most recent call last):
  File "/CCX/Py/MySql Event OFF.py", line 4, in <module>
    conn = pyodbc.connect('DRIVER={MySQL};SOCKET=/var/lib/mysql/mysql.sock;UID=rooter;PWD=sshh') 
pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnectW)')

On Win XPx64,

#cxn = MySQLdb.connect (host = "localhost",user="rooter",passwd ="sshh")

Err Msg:

File "C:\Python26\lib\site-packages\MySQLdb\__init__.py", line 19, in <module>
    import _mysql
ImportError: DLL load failed: %1 is not a valid Win32 application.

score 2 · Accepted Answer · 2011-01-14 20:43:16Z

2

Comparing odbcinst.ini and your odbc.ini show an odd value for driver in odbc.ini, you should have a dynamic library here (I think).

And why don't you use direct mysql connection (without odbc)? :

import _mysql

conn = _mysql.connect(host="localhost", user="rooter", 
                      password="sshh", db="mysql") 
conn.query("SET GLOBAL event_scheduler = ON")
conn.close()

(not tested)

edited Jan 14, 2011 at 20:43

answered Jan 12, 2011 at 5:39

user180100

Sign up to request clarification or add additional context in comments.

7 Comments

Merlin Over a year ago

above was direct connect with flaws.

Merlin Over a year ago

OK, what would be my cxn string? I got something like: cxn = pyodbc('DSN= MySQL') too work.

Merlin Over a year ago

I also want to keep code flexible to use sqlite, mysql, and sybase.

user180100 Over a year ago

added a code sample. For the flexibility, you have one query, 4 line of code so..

Merlin Over a year ago

@RC, thanks for ur help! I have connection string for both Win XPx64 and Fed 14 via ODBC. On FEd 14, got direct connect via MySQlDb. The breakdown is direct connect using py2.6 on XPx64, its installed but doesnot want to run.

|

yongguangdu · Accepted Answer · 2013-11-01 03:31:36Z

File "C:\Python26\lib\site-packages\MySQLdb__init__.py", line 19, in import _mysql ImportError: DLL load failed: %1 is not a valid Win32 application.

if Python26 && windows we need download 3 files : libguide40.dll, libmmd.dll, libmySQL.dll put the 3 files to C:\Python26\Lib\site-packages and also put the 3 files to C:\Windows\system32 then restart your system

if Python27 && windows when you setup the module ,be careful the version MySQL-python-1.2.3.win32-py2.7.exe MySQL-python-1.2.4.win32-py2.7.exe one of the 2 version is you need

Shuying · Accepted Answer · 2015-01-08 09:19:37Z

source: http://mysql-python.sourceforge.net/MySQLdb.html#some-mysql-examples

code tested:

import _mysql

conn=_mysql.connect(host="localhost", user="root", passwd="***", db="test")
conn.query("select * from test_table")
result = conn.use_result()

print result.fetch_row()

The fetch_row() function has two parameters:

maxrows limits the max number of rows returned.
how: it returns a tuple when how=0 and returns a dictionary when how=1.

For example, result.fetch(maxrows=5, how=1) returns an array (maximum size: 5) of dictionaries where the key is the column name and the value is the column value.

Collectives™ on Stack Overflow

How connect to mysql with python via odbc

3 Answers 3

7 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Related