How do I insert a PNG file into SQL using Python?

Question

0.00/5 (No votes)

See more:

I have asked this question on Stack Overflow and have received minimal help. I believe the person who tried to help me may be on the right track but I still can't seem to solve this issue.

Basically I need to take a local PNG file and insert it into a varbinary(max) field in SQL Server. I also need to use adodbapi as the library for my SQL activities within Python.

What I have tried:

What I have so far is some simple code to load the image into a byte array:
with open('path.png') as image:
f = image.read()
b = bytearray(f)

I have tried a bunch of different ways to insert this into SQL, one of which successfully inserted but was in the wrong format.

The first method which inserted but was in the wrong format is to convert the byte array to a hex string:

hexConvert = '0x'.encode('ascii') + binascii.hexlify(b)
hx1 = "'" + str(hexConvert) + "'"
hx2 = str(hx1)[1:]
hx2 = hx2[2:]

And then my query becomes:
query = "insert into myTable (imageData) values (CONVERT(varbinary(max), '" + hx2 + "'))"

The above successfully inserts into SQL but the data comes out in the format: '0x383935303...' where as I need the image to be in the format: '0x89504E470D0A...'

And those two formats are the same image, the latter was put into SQL via another program that I can not use in this instance.

The next method I've tried is to just insert the bytearray into the query as a paramter. So my byte array is saved as the variable, 'b' and my query becomes:

query = "insert into myTable (imageData) values (?)"
cursor.execute(query, b)

And this gives me the following error: '[Microsoft][ODBC Driver Manager] Invalid descriptor index'

Any help on this would be greatly appreciated. Someone mentioned to use setinputsizes but I haven't been able to figure out the correct syntax and nothing I try with that seems to make any difference.

Posted 9-Dec-20 4:07am

carltannler

Updated 9-Dec-20 6:09am

Add a Solution

Comments

Richard MacCutchan 9-Dec-20 10:36am

Don't try to convert the raw bytes into some other format. If the column type is raw binary then that is what you should store in the database. The second error message is due to invalid syntax in your execute command - check the documentation.

carltannler 9-Dec-20 11:26am

So I tried: query = "insert into myTable (imageData) values ({})".format(b)
And the execute statement is saying Syntax Error but I'm not sure what the error could be other than when the query tries to execute something from the byte array that's being put in to the query is throwing it off. I also tried using f which is just the bytes, f = image.read() and I get the same Syntax Error message. I checked the documentation and it looks like I'm doing everything correctly so I'm still not sure.

Richard MacCutchan 9-Dec-20 11:58am

Why have you changed the insert statement to store binary data as a string? As I already said, you need to store binary data as binary data, not converted to something else. The syntax error was on the execute statement, so go to the documentation and check how to add a reference to the value correctly.

carltannler 9-Dec-20 12:19pm

I've gone through the documentation and I thought this is how you insert a variable as it's own type without converting. I found a different way that has a parameter dictionary being created. So I tried that by setting paramDictionary = {"image": b} and then my query would become: query = "insert into myTable (imageData) values (%(image)s)" which would then make the execute statement: cursor.execute(query, paramDictionary), but I'm still getting an invalid syntax error. I'm not trying to change the insert statement to store a string instead of binary data I'm just trying to insert the binary data directly and I can't seem to figure it out.

Richard MacCutchan 9-Dec-20 12:51pm

Looking again at the link I gave you below, your original code appears to be OK. Did the error message provide any more information? The only thing I can possibly suggest is to remove the space between the table name and the opening parenthesis; but that is a wild guess.

carltannler 9-Dec-20 13:03pm

Yea I've already tried everything in that link, even though it's pyodbc instead of adodbapi they still work the same for the most part. The error is showing "incorrect syntax near '('" when I try do what I showed above, but when I try to use the ? and then insert the parameter in to the execute statement I get the invalid descriptor index error.

Richard MacCutchan 9-Dec-20 13:59pm

The first message implies what I suggested above. The "invalid index error" has me stumped, I am afraid. I have the possibility to try a few tests, but not until tomorrow - dinner calls.

Richard MacCutchan 10-Dec-20 5:51am

Unfortunately, I only have the ability to test with sqlite, and I am unable to reproduce your problem. Sorry but I cannot offer any further suggestions.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2020-12-09T06:09:00

Solution 1

Here is a sample of Python using ODBC: Using DataDirect ODBC drivers with Python and pyodbc[^]

Posted 9-Dec-20 6:09am

Richard MacCutchan