CODEWITHSUNDEEP
pythonsql-serverimageupload
Kevin777
Kevin777

Reputation: 45

Can't upload Images to MS Sql server via pyodbc

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

Upvotes: 0

Views: 2085

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1122132

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

Upvotes: 1

Related Questions