Passing variable to SQL statement when using psycopg2

[Julia Jacobson]

Dear python users,

For passing a variable to a SQL query for psycopg2, I use:

  my_var = xyz
  print cur.mogrify(SELECT my_values FROM my_table WHERE my_column 
= %s,(my_var,))


This returns:

  SELECT my_values FROM my_table WHERE my_column = E'xyz'

Where does the E in front of 'xyz' come from? It's probably the 
reason, why my query doesn't work.


Thanks in advance,
Julia
--
http://mail.python.org/mailman/listinfo/python-list

Re: psycopg2 for insertion of binary data to PostgreSQL database

[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',)


 Thomas Jollans wrote:


* get the record you're interested in

--
http://mail.python.org/mailman/listinfo/python-list

Re: psycopg2 for insertion of binary data to PostgreSQL database

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


 Peter Otten wrote:

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.


--
http://mail.python.org/mailman/listinfo/python-list

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
--
http://mail.python.org/mailman/listinfo/python-list