On Wed, Jul 1, 2020, at 9:52 PM, Richard Damon wrote:
> I am using SQLAlchemy ORM with a SQLite database, and many of my tables
> will have a simple integer primary key, with lots of foreign keys
> referencing them. It should improve efficiency if that integer primary
> key was the alias for the ROWID that you get by defining the column as
> INTEGER PRIMARY KEY, but it seems that with a definition of:
>
>
> class Language(Base):
> """Define Language Table."""
>
> __tablename__ = "Language"
> lang_id = Column(Integer, primary_key=True)
> lang_code = Column(String(20), unique=True)
>
> I get as the DDL:
>
> CREATE TABLE "Language" (
> lang_id INTEGER NOT NULL,
> lang_code VARCHAR(20),
> CONSTRAINT "pk_Language" PRIMARY KEY (lang_id),
> CONSTRAINT "uq_Language_lang_code" UNIQUE (lang_code)
> )
>
> which does not (at least appear to) create the needed primary key that
> is an alias for the ROWID.
I think you might be reading that phrase (not sure where you read it) too
literally. per https://www.sqlite.org/lang_createtable.html#rowid
> With one exception noted below, if a rowid table has a primary key that
> consists of a single column and the declared type of that column is "INTEGER"
> in any mixture of upper and lower case, then the column becomes an alias for
> the rowid. Such a column is usually referred to as an "integer primary key".
> The exception mentioned above is that if the declaration of a column with
> declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not
> become an alias for the rowid and is not classified as an integer primary key.
the primary key above is not DESC so it's covered as a synonym for ROWID, and
will generate incrementing integer values automatically. as long as you are
getting those incrementing keys, and you arent using the AUTOINCREMENT keyword,
you are using the rowid.
>
>
> I can't seem to find anything documented to
> do to make this happen. I would think this would be a commonly wanted
> optimization. Is there something I can do to get this? I would like to
> be able to use the ORM.
>
> --
> Richard Damon
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/c4b955a6-1dda-7fab-49a0-c84f2603822c%40Damon-Family.org.
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/55326541-468a-470b-818e-75b2f6f7a698%40www.fastmail.com.
