产品需要支持不同数据库作为数据来源，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连接数据库
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
