8

配置 ubuntu odbc 和 pypyodbc

 3 years ago
source link: https://zhiqiang.org/coding/config-odbc-pypyodbc-on-ubuntu.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

1. 安装分为三步

一定要先座这一几步，否则直接用pypyodbc会爆下面错误：

pypyodbc.DatabaseError: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'FreeTDS' : file not found")

1.1. 第一步、安装ODBC、FreeTDS模块

sudo apt-get install tdsodbc unixodbc

1.2. 第二步、修改/etc/odbcinst.ini

[FreeTDS] 
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

1.3. 第三步、修改/etc/freetds/freetds.conf

[Global] 
TDS_Version = 8.0 
client charset = UTF-8

2. pypyodbc 的安装和使用

2.1. pypyodbc 安装

pip install pypyodbc

2.2. pypyodbc 示例代码

import pypyodbc 
conn = 'Driver=FreeTDS;Server=192.168.1.2;port=1433;uid=sa;pwd=pwd1;database=db_name'
conn = pypyodbc.connect(conn) 
cursor = conn.cursor()

sql = "select * from a_table"
results = cursor.execute(sql.encode())
print(results)

2.3. pypyodbc 的中文乱码问题

一个烦心事情是 pypyodbc 查询到的中文结果都显示为问号乱码，下面的代码可以解决这个问题。但会导致无法正常获取查询结果的字段名称（curser.description结果异常）。由于修改了 sys 全局设置，目前不清楚是否还有其它副作用。

import sys
import importlib
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
importlib.reload(sys)

pyodbc.connect(connStr, unicode_results=True, charset="utf8")

3. 不需要 ODBC 了！

Python 的工具链继续完善，现在不需要 ODBC ，直接用各个数据库的工具包即可。它们的用法大同小异。

3.1. pymysql for MYSQL

import pymysql 

conn = pymysql.connect(host="1.1.1.1", 
                       port=1212, 
                       user="abc", 
                       password="bbb",
                       db="XXX")
cursor = conn.cursor()
cursor.execute(sql.encode("utf-8"))
columns = [x[0] for x in cursor.description]
data = list(cursor.fetchall())

3.2. pymssql for MSSQL

import pymssql 

conn = pymysql.connect("1.1.1.1:1213", 'username', 'password', 'dbname')
cursor = conn.cursor()
cursor.execute(sql.encode("utf-8"))
columns = [x[0] for x in cursor.description]
data = list(cursor)

3.3. cx_Oracle for ORACLE

import cx_Oracle
conn = cx_Oracle.connect("user/[email protected]:2123/dbname")
cursor = conn.cursor()
results = cursor.execute(sql.encode("utf-8"))
columns = [x[0] for x in cursor.description]
data = list(results)

Q. E. D.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK