psycopg2 for insertion of binary data to PostgreSQL database

[Julia Jacobson]

Hello everybody out there using python,

For the insertion of pictures into my PostgreSQL database [with table
foo created by SQL command "CREATE TABLE foo (bmp BYTEA)], I've written
the following script:

#!/usr/bin/python
import psycopg2
try:
conn = psycopg2.connect("dbname='postgres' user='postgres'
host='localhost' password='data'");
except:
print "I am unable to connect to the database"
cur = conn.cursor()
f = open("test.bmp", 'rb')
myfile = f.read()
try:
cur.execute("INSERT INTO foo VALUES (%s)",(buffer(myfile),))
except:
print "Insert unsuccessful"

"python script.py" runs the script without any errors or messages.
However, the SQL command "SELECT * FROM foo" returns the output "foo (0
rows)" with no entries in the table.
I'm using Python 2.7 and PostgreSQL 8.3.
Could anyone help me to find a way to pin down the problem?

Thanks in advance,
Julia

 

[D'Arcy J.M. Cain]

For the insertion of pictures into my PostgreSQL database [with table
foo created by SQL command "CREATE TABLE foo (bmp BYTEA)], I've written
the following script:

#!/usr/bin/python
import psycopg2
try:
conn = psycopg2.connect("dbname='postgres' user='postgres'
host='localhost' password='data'");
except:
print "I am unable to connect to the database"

First, bare excepts are a bad idea. Figure out what exception you
expect to catch and catch that one.

In this case, start by removing the try/except altogether and see what
the traceback says. That may explain your problem right off the bat.
If it doesn't then repost with the traceback.

 

[Peter Otten]

Hello everybody out there using python,

For the insertion of pictures into my PostgreSQL database [with table
foo created by SQL command "CREATE TABLE foo (bmp BYTEA)], I've written
the following script:

#!/usr/bin/python
import psycopg2
try:
conn = psycopg2.connect("dbname='postgres' user='postgres'
host='localhost' password='data'");
except:
print "I am unable to connect to the database"
cur = conn.cursor()
f = open("test.bmp", 'rb')
myfile = f.read()
try:
cur.execute("INSERT INTO foo VALUES (%s)",(buffer(myfile),))
except:
print "Insert unsuccessful"

"python script.py" runs the script without any errors or messages.
However, the SQL command "SELECT * FROM foo" returns the output "foo (0
rows)" with no entries in the table.
I'm using Python 2.7 and PostgreSQL 8.3.
Could anyone help me to find a way to pin down the problem?

Perhaps you need to conn.commit() your changes.

 

[Dennis Lee Bieber]

try:
cur.execute("INSERT INTO foo VALUES (%s)",(buffer(myfile),)) con.commit()
except:
print "Insert unsuccessful"

 

[Julia Jacobson]

Thanks a lot, this was the solution.
It would be greate, if you could also show me a way to extract the
inserted binary object from the table on the server to a file on a client.

Julia Jacobson wrote:

Hello everybody out there using python,

For the insertion of pictures into my PostgreSQL database [with table
foo created by SQL command "CREATE TABLE foo (bmp BYTEA)], I've written
the following script:

#!/usr/bin/python
import psycopg2
try:
conn = psycopg2.connect("dbname='postgres' user='postgres'
host='localhost' password='data'");
except:
print "I am unable to connect to the database"
cur = conn.cursor()
f = open("test.bmp", 'rb')
myfile = f.read()
try:
cur.execute("INSERT INTO foo VALUES (%s)",(buffer(myfile),))
except:
print "Insert unsuccessful"

"python script.py" runs the script without any errors or messages.
However, the SQL command "SELECT * FROM foo" returns the output "foo (0
rows)" with no entries in the table.
I'm using Python 2.7 and PostgreSQL 8.3.
Could anyone help me to find a way to pin down the problem?

Perhaps you need to conn.commit() your changes.

 

[Thomas Jollans]

Thanks a lot, this was the solution.
It would be greate, if you could also show me a way to extract the
inserted binary object from the table on the server to a file on a client.

Probably something along the lines of:

* execute an appropriate SELECT query
* get the record you're interested in
* open a file for writing
* f.write(data)
* f.close() and other clean-up code

Julia Jacobson wrote:

Hello everybody out there using python,

For the insertion of pictures into my PostgreSQL database [with table
foo created by SQL command "CREATE TABLE foo (bmp BYTEA)], I've written
the following script:

#!/usr/bin/python
import psycopg2

try:
conn = psycopg2.connect("dbname='postgres' user='postgres'

host='localhost' password='data'");

except:
print "I am unable to connect to the database"

cur = conn.cursor()
f = open("test.bmp", 'rb')
myfile = f.read()

try:
cur.execute("INSERT INTO foo VALUES (%s)",(buffer(myfile),))

except:
print "Insert unsuccessful"

"python script.py" runs the script without any errors or messages.
However, the SQL command "SELECT * FROM foo" returns the output "foo (0
rows)" with no entries in the table.
I'm using Python 2.7 and PostgreSQL 8.3.
Could anyone help me to find a way to pin down the problem?

Perhaps you need to conn.commit() your changes.

 

[Julia Jacobson]

How can I assign the result of a SQL query to a variable?
The following code snippet doesn't work:
query_result=cur.execute("SELECT column_name FROM table_name WHERE
my_variable = 'my_value'",)

 

[Peter Otten]

How can I assign the result of a SQL query to a variable?
The following code snippet doesn't work:
query_result=cur.execute("SELECT column_name FROM table_name WHERE
my_variable = 'my_value'",)

To retrieve an image from a table "images" by its name you could do
(untested):

name = "image001.jpg"
row = cur.execute("select image from images where name = %s",
(name,)).fetchone()
if row is None:
raise ValueError("no image %r found" % name)
image = row[0]