How to get data out of a postgres bytea column into a python variable using sqlalchemy?

Question

I am working with the script below. If I change the script so I avoid the bytea datatype, I can easily copy data from my postgres table into a python variable.

But if the data is in a bytea postgres column, I encounter a strange object called memory which confuses me.

Here is the script which I run against anaconda python 3.5.2:

# bytea.py

import sqlalchemy

# I should create a conn
db_s = 'postgres://dan:[email protected]/dan'
conn = sqlalchemy.create_engine(db_s).connect()

sql_s = "drop table if exists dropme"
conn.execute(sql_s)

sql_s = "create table dropme(c1 bytea)"
conn.execute(sql_s)

sql_s = "insert into dropme(c1)values( cast('hello' AS bytea) );"
conn.execute(sql_s)

sql_s = "select c1 from dropme limit 1"
result = conn.execute(sql_s)
print(result)
# <sqlalchemy.engine.result.ResultProxy object at 0x7fcbccdade80>

for row in result:
    print(row['c1'])

# <memory at 0x7f4c125a6c48>

How to get the data which is inside of memory at 0x7f4c125a6c48 ?

try select c1::text,convert_from(c1, 'UTF8') from dropme limit 1 — Vao Tsun
– Vao Tsun, Commented Jan 20, 2017 at 10:20

metmirr · Accepted Answer · 2017-01-20 14:17:08Z

2

You can cast it use python bytes()

for row in result:
    print(bytes(row['c1']))

answered Jan 20, 2017 at 14:17

metmirr

4,3222 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

snakecharmerb · Accepted Answer · 2025-05-26 07:33:10Z

It's possible to configure the SQLAlchemy data types for a query's columns by wrapping it in a text construct and calling the text object's columns method.

There are two ways to do this. Firstly, pass a mapping of column names to data types. The mapping does not need to be complete.

import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import BYTEA

engine = sa.create_engine('postgresql:///so')

with engine.connect() as conn:
    stmt = sa.text("select c1 from dropme limit 1")
    stmt = stmt.columns(c1=BYTEA)
    rows = conn.execute(stmt)

Alternatively, provide a column objects for each column as positional arguments.

with engine.connect() as conn:
    stmt = sa.text("select c1 from dropme limit 1")
    stmt = stmt.columns(sa.column('c1', BYTEA))
    rows = conn.execute(stmt)

snakecharmerb · Accepted Answer · 2025-05-26 07:40:07Z

0

Answer derived from this comment by Vao Tsun: if the column is known to contain encoded text,it may be decoded in the database then returned as a string value.

with engine.connect as conn:
    stmt = sa.text("""select c1::text,convert_from(c1, :encoding) from dropme limit 1""")
    rows = conn.execute(stmt, {'encoding': 'UTF8'})

Note that the name of the encoding should be taken from Postgresql, not Python.

answered May 26 at 7:40

community wiki

snakecharmerb

Collectives™ on Stack Overflow

How to get data out of a postgres bytea column into a python variable using sqlalchemy?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related