Closed
Description
Describe the use case
MariaDB since 10.7 supports the UUID Data Type, which can be used with SQLAlchemy (#7212), but not everywhere. The UUID type is lost when reflecting the table from an existing database.
Databases / Backends / Drivers targeted
MariaDB
Example Use
Here is a simple script showing the behaviour:
import uuid
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, UUID
engine = create_engine("mysql://root:password@127.0.0.1:3306/testdb", echo=True)
metadata_obj = MetaData()
example_table = Table(
"example",
metadata_obj,
Column("id", Integer, primary_key=True),
Column("uuid_column", UUID, default=uuid.uuid4),
)
metadata_obj.create_all(engine)
print([c.type for c in example_table.columns])
metadata_2 = MetaData()
reflected = Table("example", metadata_2, autoload_with=engine)
print([c.type for c in reflected.columns])And its output with a warning:
2023-06-28 02:05:19,730 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-06-28 02:05:19,730 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-06-28 02:05:19,731 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-06-28 02:05:19,731 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-06-28 02:05:19,732 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-06-28 02:05:19,732 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-06-28 02:05:19,733 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-06-28 02:05:19,733 INFO sqlalchemy.engine.Engine DESCRIBE `testdb`.`example`
2023-06-28 02:05:19,733 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-06-28 02:05:19,735 INFO sqlalchemy.engine.Engine
CREATE TABLE example (
id INTEGER NOT NULL AUTO_INCREMENT,
uuid_column UUID,
PRIMARY KEY (id)
)
2023-06-28 02:05:19,735 INFO sqlalchemy.engine.Engine [no key 0.00065s] ()
2023-06-28 02:05:19,742 INFO sqlalchemy.engine.Engine COMMIT
[Integer(), UUID()]
2023-06-28 02:05:19,748 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-06-28 02:05:19,749 INFO sqlalchemy.engine.Engine SHOW CREATE TABLE `example`
2023-06-28 02:05:19,749 INFO sqlalchemy.engine.Engine [raw sql] ()
C:\Users\krzys\Desktop\Programy\Python\sqlalchemy-reflection-uuid\main.py:17: SAWarning: Did not recognize type 'uuid' of column 'uuid_column'
reflected = Table("example", metadata_2, autoload_with=engine)
2023-06-28 02:05:19,751 INFO sqlalchemy.engine.Engine ROLLBACK
[INTEGER(display_width=11), NullType()]
Additional context
Alembic with the autogenerate option uses reflection to detect changes and it can't detect the type of UUID columns in MariaDB.
Metadata
Metadata
Assignees
Labels
Type
Projects
Milestone
Relationships
Participants
Activity
[-]MariaDB's UUID in reflection[/-][+]implement UUID support for MariaDB[/+]zzzeek commentedon Jun 28, 2023
it looks like we did add test support for Python uuid round trips with the UUID datatype on mariadb, as we include mariadb >=10.7.0 in test/requirements.py -> uuid_data_type(), which then takes effect in sqlalchemy/suite/test_types->NativeUUIDTest, so that part is working. The changeset that added UUID support 349a7c5 does not seem to refer to mysql or mariadb at all even though one line of code was added in the base mysql dialect.
seems like adding reflection support would be enough here.
[-]implement UUID support for MariaDB[/-][+]implement UUID support for MariaDB (seems to be just reflection)[/+]sqla-tester commentedon Jun 28, 2023
krzysdz has proposed a fix for this issue in the main branch:
Allow reflecting UUID in MariaDB https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4737
1 remaining item