Making a unique key from two non-unique fields with reflected database?

[Carl Brewer]

I'm reflecting a table into the ORM, that has no defined unique key, but
has two fields that when combined are unique - it's MySQL, but that
shouldn't matter.

Eg:

int userID and int groupId are non-unique, but when combined they are.


Can anyone point me at a simple example of how to do it? Everything
I've found by searching has been a complex example, I just want to have
the table reflected into SQLAlchemy, it's only ever going to be read-only.

out of curiosity, is there a simple way to reflect an entire database,
or do you always need to set up classes for each table?

Thank you!

Carl

[Mike Bayer]

background on mapping ORM classes to reflected tables is at https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#mapping-declaratively-with-reflected-tables and there are three general methods depending on your needs.  DeferredReflection is oriented towards explicit class setup, and automap is oriented towards "just give me pre-made classes", with much less ability to customize how the classes look and behave.

if you were using automap and needed to override PK for this one class:

from sqlalchemy.ext.automap import automap_base

from sqlalchemy import create_engine, Integer, Column

Base = automap_base()

class MyTable(Base):

    __tablename__ = 'my_table'

    userID = Column(Integer, primary_key=True)

    groupID = Column(Integer, primary_key=True)

engine = create_engine("mysql://...")

# reflect the tables

Base.prepare(autoload_with=engine)

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4df20db5-6333-9971-00f7-e9a490bfc2ea%40bl.echidna.id.au.

[Carl Brewer]

On 11/07/2022 1:19 am, Mike Bayer wrote:
> background on mapping ORM classes to reflected tables is at

> https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#mapping-declaratively-with-reflected-tables <https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#mapping-declaratively-with-reflected-tables> and there are three general methods depending on your needs.  DeferredReflection is oriented towards explicit class setup, and automap is oriented towards "just give me pre-made classes", with much less ability to customize how the classes look and behave.

>
> if you were using automap and needed to override PK for this one class:
>
> from sqlalchemy.ext.automap import automap_base
> from sqlalchemy import create_engine, Integer, Column
>
> Base = automap_base()
>
>
>
> class MyTable(Base):
>     __tablename__ = 'my_table'
>
>     userID = Column(Integer, primary_key=True)
>     groupID = Column(Integer, primary_key=True)

It's the setting of both as primary_key that does it?

Thank you

Carl

[Mike Bayer]

that's the theory anyway yes

Thank you

Carl

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/258f6797-ed5a-18c1-6fba-a05b538151d1%40bl.echidna.id.au.