代码之家 › 专栏 › 技术社区 › nosklo

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

freetds pyodbc image sql-server python

nosklo · 技术社区 · 16 年前

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

31e2fae4adbf1b2af1726e5668a3414cf46b454f 从 http://github.com/mkleehammer/pyodbc python setup.py install

微软SQL Server 2000

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

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

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

here ,便于对任何想提供帮助的人进行测试。

现在回答问题:

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

IMAGE

DATALENGTH

3 回复 | 直到 16 年前

nosklo 16 年前

SET TEXTSIZE 2147483647 /etc/freetds/freetds.conf .

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

一切都很顺利。

FreeTDS documentation says

TEXTSIZE text 和 image datatypes,设置任何返回列的最大宽度。查阅 set TEXTSIZE 在 T-SQL

根据上述解释,我认为只设置此配置选项就足够了。原来我错了,在查询中设置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

Vinay Sajip 16 年前

我认为你应该使用 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])

"""DECLARE @txtptr varbinary(16)

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

image varbinary(max) 在SQL Server的后续版本中。然而,对于较新的列类型,插入/检索的逻辑也应适用。

Collin Anderson 14 年前

4096 TEXT SET TEXTSIZE 2147483647 对我来说是固定的,但这对我来说也是固定的:

import os
os.environ['TDSVER'] = '8.0'