- edited description
document the behavior of the psycopg2 hstore extension
This is a weird one, and maybe there is something I don't understand about hstore or postgres, so bear with me.
Setting a python dictionary as the value of a text column should cause ProgrammingError, and it does, normally. However, if the hstore extension is enabled on the database, then no error is raised, and the dictionary is converted to an hstore representation and stored in the text column.
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import create_session
from sqlalchemy import Column
from sqlalchemy import Text, Integer
from sqlalchemy.ext.declarative import declarative_base
dburi = 'postgresql://username:password@localhost:5432/your_database'
engine = create_engine(dburi)
psql_command = (
"psql -h " + (engine.url.host or os.getenv('PGHOST')) + ' ' +
(('-U ' + engine.url.username) if engine.url.username else '') + ' ' +
engine.url.database
)
os.system(psql_command + " -c 'DROP EXTENSION IF EXISTS hstore'")
os.system(psql_command + " -c 'CREATE EXTENSION hstore'") # Comment out this line to see the different behavior below
session = create_session(bind=engine)
Base = declarative_base()
class T(Base):
__tablename__ = 't'
id = Column(Integer, primary_key=True)
c = Column(Text, unique=True)
Base.metadata.create_all(bind=engine)
session.begin()
t = T(c={'foo': 'bar'})
session.add(t)
try:
session.commit()
# Only succeeds when the hstore extension exists
print "Committed successfully"
except Exception as exc:
# Always happens when the hstore extension does not exist
print "Failed!"
raise
finally:
# Table only has content when the hstore extension existed
print 'Contents of "t" table:'
os.system(psql_command + " -c 'SELECT * FROM t;'")
session.close()
Base.metadata.drop_all(bind=engine)
os.system(psql_command + " -c 'DROP EXTENSION IF EXISTS hstore'")
Just to prove that this is SQLAlchemy behavior, it does not happen using raw psycopg2:
import psycopg2
conn = psycopg2.connect(database='your_database', user='postgres', host='localhost')
curs = conn.cursor()
curs.execute('DROP EXTENSION IF EXISTS hstore')
curs.execute('CREATE EXTENSION IF NOT EXISTS hstore')
curs.execute('DROP TABLE IF EXISTS t')
curs.execute('CREATE TABLE t (id SERIAL NOT NULL, c TEXT, PRIMARY KEY (id), UNIQUE (c))')
curs.execute('COMMIT')
curs.execute('BEGIN')
curs.execute('INSERT INTO t (c) VALUES (%s) RETURNING t.id', ({},))
curs.execute('COMMIT')
curs.execute('DROP EXTENSION IF EXISTS hstore')
curs.execute('DROP TABLE t')
Versions I'm using: Postgres 9.3.2, psycopg2 2.5.2, SQLAlchemy 0.9.2.
And, yes, even though it sounds crazy, we really did have a user who was trying to set dictionary objects into string columns. :)
Comments (6)
-
reporter -
reporter - edited description
-
repo owner - changed component to documentation
- changed title to document the behavior of the psycopg2 hstore extension
This is totally within psycopg2 however SQLAlchemy's documentation needs to explain the steps that are taken. Demo (note I have HSTORE turned on in my database):
import psycopg2 conn = psycopg2.connect(database='test', user='scott', host='localhost') from psycopg2 import extras extras.register_hstore(conn) curs = conn.cursor() curs.execute('DROP TABLE IF EXISTS t') curs.execute('CREATE TABLE t (id SERIAL NOT NULL, c TEXT, PRIMARY KEY (id), UNIQUE (c))') curs.execute('COMMIT') curs.execute('BEGIN') curs.execute('INSERT INTO t (c) VALUES (%s) RETURNING t.id', ({},)) curs.execute('COMMIT') curs.execute('DROP TABLE t')
-
repo owner - changed status to resolved
-
repo owner also note the new docs, disable this behavior using
create_engine(..., use_native_hstore=False)
-
reporter Ah, now I see. Thanks for the explanation, happy to prompt a clarification in the docs.
- Log in to comment