產品需要支持不同數(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
這里的文件位置要和實際的配置文件一致，如果不一致的話需要：
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
