Python-Sqlalchemy Binary Column Type HEX() and UNHEX() with autoloaded table

Question

We use sqlalchemy and inserts and updates works fine, but now we want migrate UUID from varchar to varbinary. How to do this?

Example of code now:

engine = create_engine(dburi, echo = True, pool_size=100, pool_recycle=7200)
db_session = scoped_session(sessionmaker(autocommit=True, bind=engine))
metadata = MetaData(engine)
conn = engine.connect()
....
table = Table("table", metadata, autoload=True)
result = db_session.execute(table.insert(), {"uuid":func.UNHEX("AABBCCDD"})

In output i see

INFO sqlalchemy.engine.base.Engine ('unhex(:unhex_1)',) and in db - not result of UNHEX but UNHEX(:UNHEX_1)

I have seen Python-Sqlalchemy Binary Column Type HEX() and UNHEX() but i cannot comment this and cannot understand what if we load structure, not generate it in class. Use db_sesson.execute().filter()? And how to updates when we need "where uuid=UNHEX(?)" ?

Now if i write as uuid=binascii.unhexlify(uuid), save to varbinary(32) - works, to varchar(64) with table charset=utf8 works, to carchar with charset=ascii - exception

OperationalError: (_mysql_exceptions.OperationalError) (1366, "Incorrect string value: '\\xDB\\xE7,\\x98\\xF9\\x11...' for column 'uuid' at row 1") [SQL: u'INSERT INTO callrec (record_dt, dealer_id, client_id, extension_id, caller_id, destination, file_size, record_url, uuid, record_path, duration) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2015, 12, 2, 13, 33, 51), u'1', u'12', u'6', u'000*102', u'000*103', 67244, u'', 'P\xdb\xe7,\x98\xf9\x11\xe5\xb8\x9e\xd3\xb2v\xc0\xccZ', '', 2)]

But manual write as uuid=UNHEX("AABBCCDD") already works!

Community · Accepted Answer · 2017-05-23 12:15:47Z

3

I think you are doing the insert wrongly, try this:

db_session.execute(table.insert().values(uuid=func.UNHEX("AABBCCDD")))

or this (very close to your statment):

stmt = table.insert().values(uuid=func.unhex(bindparam('hex')))
db_session.execute(stmt, {'hex': 'AABBCCDD'})

Also, if you want, you could use the solution found here, in order to do this, just override some columns:

table = Table("model", metadata, 
        Column("uuid", HashColumn(20)), 
        autoload=True)

And execute the following instruction in order to insert without use of func.*:

db_session.execute(table.insert().values(uuid='AABBCCDD'))

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Dec 2, 2015 at 17:32

iuridiniz

2,45328 silver badges31 bronze badges

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

Comments

dragonfly · Accepted Answer · 2015-12-15 09:17:34Z

0

class HashColumn(VARBINARY):
  def bind_expression(self, bindvalue):
  #bindvalue = type_coerce(bindvalue, Binary)
  return func.unhex(func.replace(bindvalue, '-', ''))

  def column_expression(self, col):
    return func.lower(func.hex(col))

def select(table_name):
  table = Table(table_name, self.metadata, Column("uuid", HashColumn(20)), autoload=True, extend_existing=True)
  select = table.select()
  ...

answered Dec 15, 2015 at 9:17

dragonfly

1882 silver badges13 bronze badges

Collectives™ on Stack Overflow

Python-Sqlalchemy Binary Column Type HEX() and UNHEX() with autoloaded table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related