How can I use UUIDs in SQLAlchemy?

Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?


Asked by: Catherine966 | Posted: 24-09-2021






Answer 1

The sqlalchemy postgres dialect supports UUID columns. This is easy (and the question is specifically postgres) -- I don't understand why the other answers are all so complicated.

Here is an example:

from sqlalchemy.dialects.postgresql import UUID
from flask_sqlalchemy import SQLAlchemy
import uuid

db = SQLAlchemy()

class Foo(db.Model):
    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)

Be careful not to miss passing the callable uuid.uuid4 into the column definition, rather than calling the function itself with uuid.uuid4(). Otherwise, you will have the same scalar value for all instances of this class. More details here:

A scalar, Python callable, or ColumnElement expression representing the default value for this column, which will be invoked upon insert if this column is otherwise not specified in the VALUES clause of the insert.

Answered by: Stuart727 | Posted: 25-10-2021



Answer 2

I wrote this and the domain is gone but here's the guts....

Regardless of how my colleagues who really care about proper database design feel about UUID's and GUIDs used for key fields. I often find I need to do it. I think it has some advantages over autoincrement that make it worth it.

I've been refining a UUID column type for the past few months and I think I've finally got it solid.

from sqlalchemy import types
from sqlalchemy.dialects.mysql.base import MSBinary
from sqlalchemy.schema import Column
import uuid


class UUID(types.TypeDecorator):
    impl = MSBinary
    def __init__(self):
        self.impl.length = 16
        types.TypeDecorator.__init__(self,length=self.impl.length)

    def process_bind_param(self,value,dialect=None):
        if value and isinstance(value,uuid.UUID):
            return value.bytes
        elif value and not isinstance(value,uuid.UUID):
            raise ValueError,'value %s is not a valid uuid.UUID' % value
        else:
            return None

    def process_result_value(self,value,dialect=None):
        if value:
            return uuid.UUID(bytes=value)
        else:
            return None

    def is_mutable(self):
        return False


id_column_name = "id"

def id_column():
    import uuid
    return Column(id_column_name,UUID(),primary_key=True,default=uuid.uuid4)

# Usage
my_table = Table('test',
         metadata,
         id_column(),
         Column('parent_id',
            UUID(),
            ForeignKey(table_parent.c.id)))

I believe storing as binary(16 bytes) should end up being more efficient than the string representation(36 bytes?), And there seems to be some indication that indexing 16 byte blocks should be more efficient in mysql than strings. I wouldn't expect it to be worse anyway.

One disadvantage I've found is that at least in phpymyadmin, you can't edit records because it implicitly tries to do some sort of character conversion for the "select * from table where id =..." and there's miscellaneous display issues.

Other than that everything seems to work fine, and so I'm throwing it out there. Leave a comment if you see a glaring error with it. I welcome any suggestions for improving it.

Unless I'm missing something the above solution will work if the underlying database has a UUID type. If it doesn't, you would likely get errors when the table is created. The solution I came up with I was targeting MSSqlServer originally and then went MySql in the end, so I think my solution is a little more flexible as it seems to work fine on mysql and sqlite. Haven't bothered checking postgres yet.

Answered by: Lucas178 | Posted: 25-10-2021



Answer 3

If you are happy with a 'String' column having UUID value, here goes a simple solution:

def generate_uuid():
    return str(uuid.uuid4())

class MyTable(Base):
    __tablename__ = 'my_table'

    uuid = Column(String, name="uuid", primary_key=True, default=generate_uuid)

Answered by: Joyce687 | Posted: 25-10-2021



Answer 4

I've used the UUIDType from the SQLAlchemy-Utils package.

Answered by: Elian319 | Posted: 25-10-2021



Answer 5

Since you're using Postgres this should work:

from app.main import db
from sqlalchemy.dialects.postgresql import UUID

class Foo(db.Model):
    id = db.Column(UUID(as_uuid=True), primary_key=True)
    name = db.Column(db.String, nullable=False)

Answered by: Lenny299 | Posted: 25-10-2021



Answer 6

Here is an approach based on the Backend agnostic GUID from the SQLAlchemy docs, but using a BINARY field to store the UUIDs in non-postgresql databases.

import uuid

from sqlalchemy.types import TypeDecorator, BINARY
from sqlalchemy.dialects.postgresql import UUID as psqlUUID

class UUID(TypeDecorator):
    """Platform-independent GUID type.

    Uses Postgresql's UUID type, otherwise uses
    BINARY(16), to store UUID.

    """
    impl = BINARY

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(psqlUUID())
        else:
            return dialect.type_descriptor(BINARY(16))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        else:
            if not isinstance(value, uuid.UUID):
                if isinstance(value, bytes):
                    value = uuid.UUID(bytes=value)
                elif isinstance(value, int):
                    value = uuid.UUID(int=value)
                elif isinstance(value, str):
                    value = uuid.UUID(value)
        if dialect.name == 'postgresql':
            return str(value)
        else:
            return value.bytes

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        if dialect.name == 'postgresql':
            return uuid.UUID(value)
        else:
            return uuid.UUID(bytes=value)

Answered by: Julia148 | Posted: 25-10-2021



Answer 7

In case anyone is interested, I've been using Tom Willis answer, but found useful to add a string to uuid.UUID conversion in the process_bind_param method

class UUID(types.TypeDecorator):
    impl = types.LargeBinary

    def __init__(self):
        self.impl.length = 16
        types.TypeDecorator.__init__(self, length=self.impl.length)

    def process_bind_param(self, value, dialect=None):
        if value and isinstance(value, uuid.UUID):
            return value.bytes
        elif value and isinstance(value, basestring):
            return uuid.UUID(value).bytes
        elif value:
            raise ValueError('value %s is not a valid uuid.UUId' % value)
        else:
            return None

    def process_result_value(self, value, dialect=None):
        if value:
            return uuid.UUID(bytes=value)
        else:
            return None

    def is_mutable(self):
        return False

Answered by: Miller793 | Posted: 25-10-2021



Answer 8

We can use UUIDType,

from sqlalchemy_utils import UUIDType
from sqlalchemy import String

class User(Base):
    id = Column(UUIDType(binary=False), primary_key=True, default=uuid.uuid4)
    name = Column(String)

For more details we can refer to the official documentation.

Answered by: Roland133 | Posted: 25-10-2021



Answer 9

You could try writing a custom type, for instance:

import sqlalchemy.types as types

class UUID(types.TypeEngine):
    def get_col_spec(self):
        return "uuid"

    def bind_processor(self, dialect):
        def process(value):
            return value
        return process

    def result_processor(self, dialect):
        def process(value):
            return value
        return process

table = Table('foo', meta,
    Column('id', UUID(), primary_key=True),
)

Answered by: Kelvin412 | Posted: 25-10-2021



Similar questions

python - Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?


python - simple update in sqlalchemy

UserTable is: id (INT) name (STR) last_login (DATETIME) Serving a web page request i have a user id in hand and I only wish to update the last_login field to 'now'. It seems to me that there are 2 ways: issue a direct SQL using db_engine (losing the mapper) OR query the user first a...


python - Nested Set Model and SQLAlchemy -- Adding New Nodes

How should new nodes be added with SQLAlchemy to a tree implemented using the Nested Set Model? class Category(Base): __tablename__ = 'categories' id = Column(Integer, primary_key=True) name = Column(String(128), nullable=False) lft = Column(Integer, nullable=False, unique=True) ...


python - sqlalchemy easy way to insert or update?

I have a sequence of new objects. They all look like similar to this: Foo(pk_col1=x, pk_col2=y, val='bar') Some of those are Foo that exist (i.e. only val differs from the row in the db) and should generate update queries. The others should generate inserts. I can think of a few ways of doing this, the best being: pk_cols = Foo.table.primary_key.keys() for f1 in foos: f2 = Foo.ge...


python - How to get sqlalchemy length of a string column

Consider this simple table definition (using SQLAlchemy-0.5.6) from sqlalchemy import * db = create_engine('sqlite:///tutorial.db') db.echo = False # Try changing this to True and see what happens metadata = MetaData(db) user = Table('user', metadata, Column('user_id', Integer, primary_key=True), Column('name', String(40)), Column('age', Integer), ...


python - SQLAlchemy filter query by related object

Using SQLAlchemy, I have a one to many relation with two tables - users and scores. I am trying to query the top 10 users sorted by their aggregate score over the past X amount of days. users: id user_name score scores: user score_amount created My current query is: top_use...


python - Group by hour in SQLAlchemy?

How do I group query results by the hour part of a datetime column in SQLAlchemy?


python - Mysql + SQLAlchemy + Pylons Issue

I'm trying to setup my models in Pylons using a MySQL backend and I keep getting the following: Traceback (most recent call last): File "/usr/local/bin/paster", line 8, in <module> load_entry_point('PasteScript==1.7.3', 'console_scripts', 'paster')() File "/usr/local/lib/python2.6/dist-packages/PasteScript-1.7.3-py2.6.egg/paste/script/command.py", line 84, in run invoke(command, comman...


python - SQLAlchemy INSERT IGNORE

How can I insert multiple data records into table ignoring duplicates. I am using SQLAlchemy. Thank you!


python - django and sqlalchemy

I'm fairly new to Django and have a basic question: I want to use an ORM that I can work with it for Django and other python projects, so the basic question is Django ORM agnostic and if so how can I use SQLAlchemy with it for example? If it's not, then what do you suggest for the above problem (using ORM objects that works with both Django and outside it)?


python - Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?


python - How to add an automatic filter to a relation with SQLAlchemy?

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table) For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to f...


python - What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?

What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?


python - SQLAlchemy and empty columns

When I try to insert a new record into the database using SQLAlchemy and I don't fill out all values, it tries to insert them as "None" (instead of omitting them). It then complains about "can't be null" errors. Is there a way to have it just omit columns from the sql query if I also omitted them when declaring the instance?


python - SQLAlchemy DateTime timezone

SQLAlchemy's DateTime type allows for a timezone=True argument to save a non-naive datetime object to the database, and to return it as such. Is there any way to modify the timezone of the tzinfo that SQLAlchemy passes in so it could be, for instance, UTC? I realize that I could just use default=datetime.datetime.utcnow; however, this is a naive time that would happily ac...


python - How can I get all rows with keys provided in a list using SQLalchemy?

I have sequence of IDs I want to retrieve. It's simple: session.query(Record).filter(Record.id.in_(seq)).all() Is there a better way to do it?


python - How can I order objects according to some attribute of the child in sqlalchemy?

Here is the situation: I have a parent model say BlogPost. It has many Comments. What I want is the list of BlogPosts ordered by the creation date of its' Comments. I.e. the blog post which has the most newest comment should be on top of the list. Is this possible with SQLAlchemy?


python - SQLAlchemy - INSERT OR REPLACE equivalent

does anybody know what is the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language? Many thanks -- honzas


python - Defining a table with sqlalchemy with a mysql unix timestamp

Background, there are several ways to store dates in MySQ. As a string e.g. "09/09/2009". As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds). As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps. As a MySQL Date field, another mysql spec...


python - How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?

Suppose I have an engine pointing at MySQL database: engine = create_engine('mysql://arthurdent:answer42@localhost/dtdb', echo=True) I can populate dtdb with tables, FKs, etc by: metadata.create_all(engine) Is there an easy way to generate the SQL file that contains all the DDL statements instead of actually applying these DDL sta...






Still can't find your answer? Check out these communities...



PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python



top