0

i'm trying to upload an image to MS SQL web-server in Linux(raspbian) environment using python language. so far i had able connect to MS Sql and also i had create a table. And im using pyodbc. 

#! /user/bin/env python
import pyodbc 

dsn = 'nicedcn'
user = myid
password = mypass
database = myDB

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)
cursor = cnxn.cursor()

string = "CREATE TABLE Database1([image name] varchar(20), [image] varbinary(max))"
cursor.execute(string)
cnxn.commit()

this part complied without any error. that means i have successfully created a table isn't? or is there any issue?

i try to upload image as this way. 

with open('new1.jpg','rb') as f:
            bindata = f.read()
cursor.execute("insert into Database1(image name, image) values (?,?)", 'new1', bindata)
cnxn.commit()

i get the error on this part. and it pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS] [SQL Server] Satement(s) could not be prepared. (8180) (SQLParamData)')

can some one help me please. thank you

Kevin777
  • 45
  • 1
  • 10

1 Answers1

1

Your parameters must be passed in as one sequence, not as two separate arguments. A tuple will do nicely here:

cursor.execute(
    "insert into Database1([image name], image) values (?,?)",
    ('new1', pyodbc.Binary(bindata)))

Note that you also need to quote the image name column correctly, and wrap the data in a pyodbc.Binary() object; this will produce the right datatype for your Python version (bytearray or bytes).

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • thank for you help but i'm still getting that same error – Kevin777 Jun 28 '14 at 18:46
  • @Kevin777: ah, yes, your `image name` column also needs quoting. – Martijn Pieters Jun 28 '14 at 20:43
  • thanks for you help. i think it works but i'm still getting error. it says pyodbc.ProgrammingError: ('22018', '[22018] [FreeTDS] [SQL Server]Operand type clash: text is incompatible with varbinary(max) (206) (SQLParamData)')... do i need to change my image coloum data type into image or blob? can i upload image as jpg directly in to the database?? – Kevin777 Jun 29 '14 at 17:02
  • @Kevin777: right, you need to use a `pyodbc.Binary()` object. – Martijn Pieters Jun 29 '14 at 23:05
  • sir there is another issue, http://stackoverflow.com/questions/24491421/cant-insert-date-and-time-to-sql-server-via-pyodbc/24492737?noredirect=1#comment37915225_24492737 help me please – Kevin777 Jun 30 '14 at 15:00