Linux配置ODBC连接Mysql

1、安装mysql

2、安装unixodbc

odbcinst  -j  查询unixodbc版本以及配置文件路径

3、安装mysql-connector-odbc

####下载
wget https://cdn.mysql.com/archives/mysql-connector-odbc-9.0/mysql-connector-odbc-9.0.0-1.el7.x86_64.rpm
####安装
rpm -ivh mysql-connector-odbc-9.0.0-1.el7.x86_64.rpm

4、 建立ODBC数据源

####vim  /usr/local/etc/odbcinst.ini

[MySQL ODBC 9.0 Unicode Driver]
Description=mysql odbc for linux
iDRIVER64=/usr/lib64/libmyodbc9w.so
Setup64=/usr/lib64/libmyodbc9w.so
UsageCount=1

####vim  /usr/local/etc/odbc.ini

[mydsn]
Description = My MySQL Data Source
Driver      = /usr/lib64/libmyodbc9w.so
Server      = localhost
Port        = 3306
Database    = mysql
User        = root
Password    = password
Option      = 3

5、测试连接

####搜索unixodbc的isql

find  / -name  isql   

####连接mysql数据源  isql  mydsn root password

/usr/bin/isql  mydsn root password

6、python通过odbc连接mysql

####建表

create table your_table(id int,name varchar(20));

####插入数据

insert into your_table values(1,'wangwu');

#####用例
import pyodbc

# 使用DSN连接
conn_str = (
    r'DRIVER=libmyodbc9w.so;'
    r'SERVER=localhost;'
    r'PORT=3306;'
    r'DATABASE=mysql;'
    r'UID=root;'
    r'PWD=password'
)

# 或者使用DSN名称连接
# conn_str = 'DSN=MyDSN;'

try:
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM your_table")
    rows = cursor.fetchall()
    for row in rows:
        print(row)
finally:
    if conn:
        conn.close()