psycopg2 for insertion of binary data to PostgreSQL database

J

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

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

Peter Otten

Julia said:
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.
 
J

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

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
Peter said:
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.
 
J

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'",)
 
P

Peter Otten

Julia said:
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,818
Messages
2,569,736
Members
45,707
Latest member
onlinereputationrepairser

Latest Threads

Top