首页 / 问题 / 正文

在Ubuntu上使用pyodbc向SQL Server插入图像字段

7 投票
3 回答
8267 浏览
提问于 2025-04-15 12:34

我正在使用Ubuntu 9.04

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

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

我从http://github.com/mkleehammer/pyodbc获取了pyodbc的版本31e2fae4adbf1b2af1726e5668a3414cf46b454f，并使用"python setup.py install"安装了它。

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

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

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()

到目前为止，一切正常工作。我在服务器上使用SQL Server的企业管理器，新的表格已经存在。现在我想在表格中插入一些数据。

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的回答（谢谢他），我把代码改成了：

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('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

我把上面的所有代码放在一个文件里，方便任何想要帮助的人进行测试，链接在这里。

现在问题来了：

我想要Python代码将一个图片文件插入到mssql中。我想查询这个图片并展示给用户。

我不在乎mssql中的列类型。我在示例中使用了"IMAGE"列类型，但任何二进制或blob类型都可以，只要我能完整地取回我插入的文件的二进制数据。Vinay Sajip在下面提到，这在SQL SERVER 2000中是推荐的数据类型。

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

我该如何解决这个问题？

编辑：Vinay Sajip的回答给了我一个提示，使用pyodbc.Binary来处理字段。我已经相应地更新了问题。谢谢Vinay Sajip！

Alex Martelli的评论让我想到使用DATALENGTH这个MS SQL函数来测试数据是否完全加载到列中。谢谢Alex Martelli！

数据库 ubuntu 数据插入 sql server 数据查询 pyodbc 二进制数据 blob

3 个回答

1

我之前在 TEXT 字段上遇到过类似的 4096 截断问题，使用 SET TEXTSIZE 2147483647 解决了这个问题。不过，这个方法也同样对我有效：

import os
os.environ['TDSVER'] = '8.0'
回答于 2025-04-15 由 Python大师
分享 举报
3

我觉得你应该使用一个 pyodbc.Binary 实例来包装数据：

cur.execute('INSERT INTO testing (myimage) VALUES (?)', (pyodbc.Binary(data),))

取数据的时候应该这样：

cur.execute('SELECT myimage FROM testing')
print "image bytes: %r" % str(cur.fetchall()[0][0])

更新：问题出在插入数据上。把你的插入 SQL 改成下面这样：

"""DECLARE @txtptr varbinary(16)

INSERT INTO testing (myimage) VALUES ('')
SELECT @txtptr = TEXTPTR(myimage) FROM testing 
WRITETEXT testing.myimage @txtptr ?
"""

我也修正了我在取数据代码中使用 value 属性的错误。

通过这个改动，我可以把一个 320K 的 JPEG 图片插入到数据库中（取出来的数据和插入的数据是一样的）。

注意：image 数据类型已经不推荐使用了，后来的 SQL Server 版本用 varbinary(max) 替代。不过，对于新的列类型，插入和取数据的逻辑应该还是一样的。

回答于 2025-04-15 由 Python大师
分享 举报
5

哎呀，就在我发布悬赏后，我找到了解决办法。

你需要在查询中使用 SET TEXTSIZE 2147483647，此外还要在 /etc/freetds/freetds.conf 文件中进行文本大小的配置。

我使用了

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

然后一切都正常了。

奇怪的是，FreeTDS 的文档中关于文本大小配置的说明是这样的：

默认的 TEXTSIZE 值，以字节为单位。对于 text 和 image 数据类型，设置返回列的最大宽度。请参考你服务器的 T-SQL 文档中的 set TEXTSIZE。

文档中还提到，最大值（也是默认值）是 4,294,967,295。不过，当我试图在查询中使用这个值时却出错了，我能在查询中使用的最大数字是 2,147,483,647（也就是一半）。

根据这个解释，我原以为只设置这个配置选项就够了。结果我错了，在查询中设置 TEXTSIZE 才解决了问题。

下面是完整的可运行代码：

#!/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
回答于 2025-04-15 由 Python大师
分享 举报

撰写回答