使用 Python、FreeTDS 和 pyodbc 从 Raspberry Pi 3 查询 MSSQL Server 2012 [英] Querying MSSQL server 2012 from a Raspberry Pi 3 using Python, FreeTDS and pyodbc

问题描述

我正在尝试使用 Python 作为我在 raspberry Pi3 上的脚本语言来查询 MSSQL Server 2012.

我需要创建一个应用程序来查询 MSSQL 服务器并返回一些需要在 H.M.I. 上显示的值.我选择了 Raspberry Pi 平台来开发这个使用 Python 作为编程语言的解决方案.我在 Windows 7 PC 上使用 PyCharm 创建了脚本，并且一切正常.当我将其移至 Raspberry 平台时，它不起作用.

我使用 pyODBC 进行连接和查询，并使用 FreeTDS 作为驱动程序.我使用以下程序进行设置:

 sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbcpip3 安装 pyODBC

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

 [名称]主机 = ip 地址端口 = 1433tds 版本 = 7.4实例 = dbInstanceName

然后我转到命令行并使用以下命令测试连接:tsql -S NAME -U username.命令行然后提示 Password: 所以我输入密码，我得到以下内容:

 语言环境是enGB.UTF-8"语言环境字符集是UTF-8"使用默认字符集UTF-8"1>

由于没有错误，我只能假设这有效?

然后我设置/etc/odbcinst.ini 文件如下:

 [FreeTDS]驱动程序 =/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so

然后我设置/etc/odbc.ini 文件如下:

 [NAME1]驱动程序 =/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so说明 = MSSQL 服务器跟踪 = 否服务器 = ip地址数据库 = dbName端口 = 1433TDS_版本 = 7.4

然后我在命令行中使用 isql 函数对此进行了测试:isql NAME1 user password 并得到以下提示:

+-------------------------------------------------+|连接的！||sql语句|帮助 [表名]|放弃|+-------------------------------------------------+SQL>

所以我输入了 select getDate() 并返回日期和时间.

但是在 Python 中我仍然无法获得连接，我在解释器中输入了以下内容:

导入pyodbcconn = pyodbc.connect('DRIVER=FreeTDS;SERVER=NAME;DATABASE=dbName;UID=user;PWD=password')

然后当我输入这个 cu = conn.cursor() 我得到一个错误:

AttributeError: 'NoneType' 对象没有属性游标

解决方案

已在 Raspberry pi2 上测试 &Python 3 与 Raspbian &MS SQL 服务器 2008

<块引用>

确保您的 APT-Get 库和Python版本是最新的"

sudo apt-get dist-upgrade须藤 apt-get 安装 python3

<块引用>

运行以下命令安装需求

sudo apt-get install unixodbc须藤 apt-get 安装 unixodbc-devsudo apt-get install freetds-devsudo apt-get install tdsodbc须藤 apt-get 安装 freetds-bin

<块引用>

在终端中，现在运行:(使用'pip3'，因为由于某些错误，pyodbc不会为pip(python 2)安装)

sudo pip3 install pyodbc须藤 apt-get 安装 python-pyodbc

<块引用>

像这样更改 freeTDS.conf

sudo nano/etc/freetds/freetds.conf添加一个像这样的块:[sqlserver]host = 182.172.2.2 #远程Sql Server的IP地址端口 = 1433 # 这是默认值tds version = 7.0 # 这是我发这个的时候instance = Test1 # 你的数据库名称

<块引用>

然后设置/etc/odbcinst.ini文件如下:

[FreeTDS]说明 = FreeTDS unixODBC 驱动程序驱动程序 =/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so设置 =/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so使用次数 = 1

<块引用>

然后设置/etc/odbc.ini 文件如下:

[NAME1]驱动程序 =/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so说明 = MSSQL 服务器跟踪 = 否Server = Server2 # Sql Server 的 IP 或主机名数据库 = Test1 # 数据库名称端口 = 1433 # 这是默认值TDS_版本 = 7.4

<块引用>

现在用这个命令测试连接(第二个你应该获得对Sql服务器的命令行访问

tsql -S sqlserver -U 用户名isql NAME1 用户密码"

最后是代码部分:

导入pyodbcconn = pyodbc.connect('DRIVER={FreeTDS};Server=Server2;PORT=1433;DATABASE=Test1;UID=user;PWD=pass;TDS_Version=7.2;')游标 = conn.cursor()cursor.execute("Select * from Table1")对于 cursor.fetchall() 中的行:打印(行)

最后，如果没有任何效果，试试这个:

sudo dpkg-reconfigure tdsodbc

I am trying to query MSSQL server 2012 using Python as my scripting language on a raspberry Pi3.

I have the need to create an application that will query MSSQL server and return some values that need to be displayed on a H.M.I. I chose the Raspberry Pi platform to develop this solution using Python as the programming language. I created the script using PyCharm on a Windows 7 PC and all worked well. When I moved it to the Raspberry Platform it didn't work.

I am using pyODBC to do the connecting and querying and FreeTDS as the driver. I used the following procedure to set this up:

 sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
 pip3 install pyODBC

Configured the /etc/freetds.conf file as follows

 [NAME]
 host = ipAddress
 port = 1433
 tds version = 7.4
 instance = dbInstanceName

I then went to the command line and tested the connection with: tsql -S NAME -U username. The command line then prompts with Password: so I typed the password in and I got the following:

 locale is "enGB.UTF-8"
 locale charset is "UTF-8"
 using default charset "UTF-8"
 1>

As there are no errors present I can only assume that this has worked?

I then set up the /etc/odbcinst.ini file as follows:

 [FreeTDS]
 Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so

I then setup the /etc/odbc.ini file as follows:

 [NAME1]
 Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
 Description = MSSQL Server
 Trace = No
 Server = ipAddress
 Database = dbName
 Port = 1433
 TDS_Version = 7.4

I then tested this with the isql function in the command line: isql NAME1 user password and I got the following prompt:

+-------------------------------------------------+
| Connected!
|
| sql-statement
| help [tablename] 
| quit
|
+-------------------------------------------------+

SQL> 

so I typed in select getDate() and the date and time returned.

However within Python I still can't get a connection, I typed the following into the interpreter:

import pyodbc

conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=NAME;DATABASE=dbName;UID=user;PWD=password')

Then when I type this cu = conn.cursor() I get an error:

AttributeError: 'NoneType' object has no attribute cursor

解决方案

Tested on Raspberry pi2 & Python 3 with Raspbian & MS Sql server 2008

Make sure your APT-Get library & Python Version is up to date "

sudo apt-get dist-upgrade
Sudo apt-get install python3

Run following commands to install requirements

sudo apt-get install unixodbc
sudo apt-get install unixodbc-dev
sudo apt-get install freetds-dev
sudo apt-get install tdsodbc
sudo apt-get install freetds-bin 

In terminal, now run :(use 'pip3' because pyodbc wouldn’t install for pip (python 2) due to some errors)

sudo pip3 install pyodbc
sudo apt-get install python-pyodbc

Change freeTDS.conf like this

sudo nano /etc/freetds/freetds.conf

Add a block like this :

[sqlserver]
      host = 182.172.2.2    # Remote Sql Server's IP addr
      port = 1433           # this is default
      tds version = 7.0     # this is by the time i post this
      instance = Test1      # your Database name 

Then set up the /etc/odbcinst.ini file as follows:

[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Setup = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
UsageCount = 1

Then setup the /etc/odbc.ini file as follows:

[NAME1]
Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Description = MSSQL Server
Trace = No
Server = Server2      # IP or host name of the Sql Server
Database = Test1      # DataBase Name
Port = 1433           # This is default
TDS_Version = 7.4

Now test the connection with this commands (with second one you should get command line access to Sql server

tsql -S sqlserver -U username
isql NAME1 user 'password'

And finally the code part :

import pyodbc
conn = pyodbc.connect('DRIVER={FreeTDS};Server=Server2;PORT=1433;DATABASE=Test1;UID=user;PWD=pass;TDS_Version=7.2;')
cursor = conn.cursor()
cursor.execute("Select * from Table1")
for row in cursor.fetchall():
    print (row)

Finally if nothing worked try this :

sudo dpkg-reconfigure tdsodbc

这篇关于使用 Python、FreeTDS 和 pyodbc 从 Raspberry Pi 3 查询 MSSQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！