This post will show you how to use RAW data types from cx_Oracle dialect in SQLAlchemy to create unique row IDs that are generated by SYS_GUID().
This lets you use Oracle’s SYS_GUID() function to create unique IDs for rows without Sequences. The added advantage is that each ID is unique, even among tables. There are many discussions about system performance regarding this technique, but the general consensus is that this is as fast or faster than Integers and Sequences.
The Setup
First, the RAW dialect is required from the cx_Oracle package. SQLAlchemy has knowledge of most basic cx_Oracle types, but RAW is not there by default.
In [1]: from sqlalchemy.dialects.oracle import RAW
RAW data in Oracle is Binary data. We will have to convert between binary and ascii-hex so humans can use the values.
In [2]: import binascii
Your SQLAlchemy model object will look like this.
This code sample assumes you already have a declarative base and a Base class. For basics on using SQLAlchemy, see their own documentation: http://www.sqlalchemy.org/
class MyTable(Base):
__tablename__ = 'my_table'
__table_args__ = (
Index('my_table_uk1', 'name', 'value', unique=True),
)
id = Column(RAW, primary_key=True, server_default=text("SYS_GUID()"))
name = Column(Unicode(100), nullable=False)
value = Column(Unicode(100), nullable=False)
That’s all there is to the setup. You have a model that supports RAW with an ‘id’ column that is automatically a call to Oracle’s internal SYS_GUID() function.
Inserting Data
You can insert a row by putting a Binary value in to the id column, but why bother? Just leave it out. If you create this object:
{
'name': 'Larry',
'value': 'Programmer'
}
You can query it and see that an ID was generated:
In [3]: for value in Session.query(models.MyTable.id).distinct():
...: print(value)
(b'L\xeaH\xde\x88|#F\xe0S\xae\xa4\xcc\n\xf6@')
It works! However, that value sure is ugly.
Making it Readable
To convert that value to something your users can reference as a unique ID.
In [4]: values = v2_session.read_session.query(models.MyTable).all()
In [5]: values
Out[5]:
[<models.MyTable at 0x2b0bbbaaf518>]
In [6]: v = values[0]
In [7]: v
Out[7]: <models.MyTable at 0x2b0bbbaaf438>
In [8]: v.name
Out[8]: 'Larry'
In [9]: v.value
Out[9]: 'Programmer'
In [10]: v.id
Out[10]: b'L\xeaH\xde\x88|#F\xe0S\xae\xa4\xcc\n\xf6@'
In [11]: v.id.hex()
Out[11]: '4cea48de887c2346e053aea4cc0af640'
The RAW object has a hex() method that translates nicely in to a string GUID your users and APIs can work with.
From Readable Back to RAW
Now lets turn that string back in to a RAW value that SQLAlchemy can use to query the database.
In [12]: column_id = binascii.a2b_hex('4cea48de887c2346e053aea4cc0af640')
In [13]: values = Session.query(models.MyTable).filter_by(id=column_id).all()
In [14]: values
Out[14]: [<models.MyTable at 0x2b0bbbaaf438>]
There you have it.
Did you find this post useful or have questions or comments? Please let me know!