在ubuntu上使用pyodbc在SQL Server上插入图像字段 [英] using pyodbc on ubuntu to insert a image field on SQL Server

问题描述

我正在使用 Ubuntu 9.04

我安装了以下软件包版本：

I have installed the following package versions:

unixodbc and unixodbc-dev: 2.2.11-16build3
tdsodbc: 0.82-4
libsybdb5: 0.82-4
freetds-common and freetds-dev: 0.82-4
python2.6-dev

我已配置 /etc/unixodbc.ini 这样：

[FreeTDS]
Description             = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout               = 
CPReuse         = 
UsageCount              = 2

我已配置 / etc / freetds / freetds.conf 像这样：

[global]
    tds version = 8.0
    client charset = UTF-8
    text size = 4294967295

I抢了pyodbc修订版 31e2fae4adbf1b2af1726e5668a3414cf46b 454f 来自 http://github.com/mkleehammer/pyodbc 并使用 python setup.py install <安装

I have grabbed pyodbc revision 31e2fae4adbf1b2af1726e5668a3414cf46b454f from http://github.com/mkleehammer/pyodbc and installed it using "python setup.py install"

我的本​​地网络上安装了 Microsoft SQL Server 2000 的Windows机器，并且正在监听本地IP地址10.32.42.69。我有一个名为Common的空数据库。我的用户名为sa，密码为secret，具有完全权限。

I have a windows machine with Microsoft SQL Server 2000 installed on my local network, up and listening on the local ip address 10.32.42.69. I have an empty database created with name "Common". I have the user "sa" with password "secret" with full privileges.

我使用以下python代码设置连接：

I am using the following python code to setup the connection:

import pyodbc
odbcstring = "SERVER=10.32.42.69;UID=sa;PWD=secret;DATABASE=Common;DRIVER=FreeTDS"
con = pyodbc.connect(odbcstring)
cur = con.cursor()

cur.execute("""
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'testing')
   DROP TABLE testing
""")
cur.execute('''
CREATE TABLE testing (
    id INTEGER NOT NULL IDENTITY(1,1), 
    myimage IMAGE NULL, 
    PRIMARY KEY (id)
)
    ''')
con.commit()

到目前为止，所有工作。我在服务器上使用过SQLServer的企业管理器，新表就在那里。
现在我想在表格中插入一些数据。

Everything WORKS up to this point. I have used SQLServer's Enterprise Manager on the server and the new table is there. Now I want to insert some data on the table.

cur = con.cursor()
# using web data for exact reproduction of the error by all.
# I'm actually reading a local file in my real code.
url = 'http://www.forestwander.com/wp-content/original/2009_02/west-virginia-mountains.jpg'
data = urllib2.urlopen(url).read()

sql = "INSERT INTO testing (myimage) VALUES (?)"

现在在我原来的问题上，我在使用 cur.execute（sql，（data，））时遇到了麻烦，但现在我已经编辑了这个问题，因为关注了Vinay Sajip的回答下面（感谢），我已将其更改为：

Now here on my original question, I was having trouble using cur.execute(sql, (data,)) but now I've edited the question, because following Vinay Sajip's answer below (THANKS), I have changed it to:

cur.execute(sql, (pyodbc.Binary(data),)) 
con.commit()

插入工作正常。我可以使用以下测试代码确认插入数据的大小：

And insertion is working perfectly. I can confirm the size of the inserted data using the following test code:

cur.execute('SELECT DATALENGTH(myimage) FROM testing WHERE id = 1')
data_inside = cur.fetchone()[0]
assert data_inside == len(data)

哪个传递完美 !!!

现在问题在于检索数据。

Now the problem is on retrieval of the data back.

我正在尝试常见方法：

cur.execute('SELECT myimage FROM testing WHERE id = 1')
result = cur.fetchone()
returned_data = str(result[0]) # transforming buffer object
print 'Original: %d; Returned: %d' % (len(data), len(returned_data))
assert data == returned_data

然而失败!!

Original: 4744611; Returned: 4096
Traceback (most recent call last):
  File "/home/nosklo/devel/teste_mssql_pyodbc_unicode.py", line 53, in <module>
    assert data == returned_data
AssertionError

我已经把所有代码以上单个文件此处，以方便测试任何想要帮助的人。

I've put all the code above in a single file here, for easy testing of anyone that wants to help.

现在提出问题：

我希望python代码将图像文件插入到mssql中。我想要查询图像并将其显示给用户。

I want python code to insert an image file into mssql. I want to query the image back and show it to the user.

我不关心mssql中的列类型。我在示例中使用 IMAGE 列类型，但任何二进制/ blob类型都可以，只要我获取未插入的文件的二进制数据。 Vinay Sajip在下面说这是SQL SERVER 2000中首选的数据类型。

I don't care about the column type in mssql. I am using the "IMAGE" column type on the example, but any binary/blob type would do, as long as I get the binary data for the file I inserted back unspoiled. Vinay Sajip said below that this is the preferred data type for this in SQL SERVER 2000.

现在插入的数据没有错误，但是当我检索数据时，只有返回4k。 （数据在4096上被截断）。

The data is now being inserted without errors, however when I retrieve the data, only 4k are returned. (Data is truncated on 4096).

我该如何工作？

编辑：下面的Vinay Sajip回答给了我一个在场上使用pyodbc.Binary的提示。我已相应更新了这个问题。感谢Vinay Sajip！

EDITS: Vinay Sajip's answer below gave me a hint to use pyodbc.Binary on the field. I have updated the question accordingly. Thanks Vinay Sajip!

Alex Martelli的评论让我想到了使用 DATALENGTH MS SQL函数测试是否数据已完全加载到列上。谢谢亚历克斯马尔泰利！

Alex Martelli's comment gave me the idea of using the DATALENGTH MS SQL function to test if the data is fully loaded on the column. Thanks Alex Martelli !

推荐答案

嗯，在提供赏金之后，我找到了解决方案。

Huh, just after offering the bounty, I've found out the solution.

除了 / etc中的文字大小配置选项外，您还必须在查询中使用 SET TEXTSIZE 2147483647 /freetds/freetds.conf 。

You have to use SET TEXTSIZE 2147483647 on the query, in addition of text size configuration option in /etc/freetds/freetds.conf.

我用过

cur.execute('SET TEXTSIZE 2147483647 SELECT myimage FROM testing WHERE id = 1')

一切正常。

奇怪的是有关文字大小的 FreeTDS文档说明配置选项：

Strange is what FreeTDS documentation says about the text size configuration option:

默认值 TEXTSIZE ，以字节为单位。对于 text 和 image datatypes，设置任何返回列的最大宽度。参看在服务器的 T-SQL 文档中设置TEXTSIZE 。

default value of TEXTSIZE, in bytes. For text and image datatypes, sets the maximum width of any returned column. Cf. set TEXTSIZE in the T-SQL documentation for your server.

配置还说最大值（和默认值）是4,294,967,295。但是当我在查询中尝试使用该值时，我得到一个错误，我在查询中可以使用的最大数字是2,147,483,647（一半）。

The configuration also says that the maximum value (and the default) is 4,294,967,295. However when trying to use that value in the query I get an error, the max number I could use in the query is 2,147,483,647 (half).

从这个解释我认为只设置此配置选项就足够了。事实证明我错了，在查询中设置TEXTSIZE修复了问题。

From that explanation I thought that only setting this configuration option would be enough. It turns out that I was wrong, setting TEXTSIZE in the query fixed the issue.

以下是完整的工作代码：

Below is the complete working code:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import pyodbc
import urllib2

odbcstring = "SERVER=10.32.42.69;UID=sa;PWD=secret;DATABASE=Common;DRIVER=FreeTDS"
con = pyodbc.connect(odbcstring)
cur = con.cursor()

cur.execute("""
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'testing')
   DROP TABLE testing
""")

cur.execute('''
CREATE TABLE testing (
    id INTEGER NOT NULL IDENTITY(1,1), 
    myimage IMAGE NULL,
    PRIMARY KEY (id)
)
    ''')

con.commit()
cur = con.cursor()
url = 'http://www.forestwander.com/wp-content/original/2009_02/west-virginia-mountains.jpg'
data = urllib2.urlopen(url).read()

sql = "INSERT INTO testing (myimage) VALUES (?)"
cur.execute(sql, (pyodbc.Binary(data),))
con.commit()

cur.execute('SELECT DATALENGTH(myimage) FROM testing WHERE id = 1')
data_inside = cur.fetchone()[0]
assert data_inside == len(data)

cur.execute('SET TEXTSIZE 2147483647 SELECT myimage FROM testing WHERE id = 1')
result = cur.fetchone()
returned_data = str(result[0])
print 'Original: %d; Returned; %d' % (len(data), len(returned_data))
assert data == returned_data

这篇关于在ubuntu上使用pyodbc在SQL Server上插入图像字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！