pyodbc连接mysql和mssql

产品需要支持不同数据库作为数据来源，python的pyodbc可以支持这个功能，为了使用odbc，需要安装unixodbc，libmyodbc（mysql），freetds（ms sql server），还需要做一些配置，记录如下。

两种方式：

sudo apt-get install unixODBC unixODBC-dev


或者下载源码安装：

./configure
make
sudo make install
连接MySql

安装libmyodbc

sudo apt-get install libmyodbc


找到安装位置

find / |grep libmyodbc.so


配置odbcinst.ini

该文件一般在/etc/odbcinst.ini，配置如下：

[MySQL]
Description = MySQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
FileUsage = 1


libmyodbc的位置就是上一步找到的位置

配置odbc.ini

该文件一般在/etc/odbc.ini，配置如下：

[Mysql]
Description = The Database for Mysql
Trace = On
TraceFile = stderr
Driver = MySQL 这里的MySQL就是上一步odbcinst.ini里的名字
SERVER = localhost
USER =
PASSWORD =
PORT = 3306
DATABASE = test


测试连接Mysql

ly@ly-VirtualBox:/tmp$ isql Mysql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
连接MS Sql server

安装freetds

sudo apt-get install freetds-dev tdsodbc


类似mysql找到安装位置

配置freetds

配置文件/etc/freetds/freetds.conf

[tdsserver]
host = 127.0.0.1
port = 1401
tds version = 7.0
client charset = UTF-8


测试freetds连接数据库

tsql -S tdsserver -U username -P passwd


配置odbcinst.ini

类似上面mysql的配置如下：

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1


配置odbc.ini

类似上面mysql的配置如下：

[sqlserverdatasource]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = On
Servername = tdsserver 这里的值是/etc/freetds/freetds.conf里面的名字
USER =
PASSWORD =
Database = test


测试连接Mssql

ly@ly-VirtualBox:/tmp$ isql sqlserverdatasource *username passwd*
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
问题

[unixODBC][Driver Manager]Data source name not found, and no default

ly@ly-VirtualBox:/tmp$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8


这里的文件位置要和实际的配置文件一致，如果不一致的话需要：

export ODBCINI= /etc/odbc.ini
export ODBCSYSINI= /etc
python代码

import pyodbc
from sqlalchemy import create_engine
import pandas as pd
encoding = "utf8"
database_type = "mysql"
user_name = ""
password = ""
dsn = "Mysql"
dsn = "sqlserverdatasource"
database_type = "mssql"
user_name = ""
password = ""
url = "{}+pyodbc://{}:{}@{}".format(database_type, user_name, password, dsn)
engine=create_engine(url, encoding=encoding, echo=True)
print(engine.table_names())
df = pd.read_csv('/tmp/train.csv')
df.to_sql(con=engine, name="test", if_exists='replace',index=False)
sql = 'select * from test'
data = pd.read_sql(sql, engine)
print(data)
参考

https://tryolabs.com/blog/2012/06/25/connecting-sql-server-database-python-under-ubuntu/
https://docs.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-docker