產(chǎn)品需要支持不同數(shù)據(jù)庫作為數(shù)據(jù)來源，python的pyodbc可以支持這個功能，為了使用odbc，需要安裝unixodbc，libmyodbc（mysql），freetds（ms sql server），還需要做一些配置，記錄如下。

安裝unixodbc

兩種方式：

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連接數(shù)據(jù)庫

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

這里的文件位置要和實(shí)際的配置文件一致，如果不一致的話需要：

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