CODEWITHSUNDEEP
pythonsqlitesqlalchemy
SATW
SATW

Reputation: 95

Alternative to rowid in sqlite in sqlalchemy

By rowid i mean the implicit column in all sqlite tables which stores the row number.
I can use rowid in sqlite using

 c.execute('SELECT rowid,* FROM table_name):

What can i do to use the rowid value in sqlalchemy?

Upvotes: 5

Views: 2660

Answers (2)

Patrick
Patrick

Reputation: 33

You can simply tell sqlalchemy not to create that field with the system flag, and you can use it like normal in queries.

Example using more modern mapped columns:

...
class Foo(Base):
    bar: Mapped[int] = mapped_column(Integer())
    rowid: Mapped[int] = mapped_column(Integer(), system=True)
 
first_foo = session.query(Foo).filter(Foo.rowid == 1)
#From the :param hints of sqlalchemy.Column:
'''
        :param system: When ``True``, indicates this is a "system" column,
             that is a column which is automatically made available by the
             database, and should not be included in the columns list for a
             ``CREATE TABLE`` statement.

             For more elaborate scenarios where columns should be
             conditionally rendered differently on different backends,
             consider custom compilation rules for :class:`.CreateColumn`.
'''

Furthermore, if you want to access rowid on every table, then I would suggest putting the rowid attribute on the Base class instead of all of them individually:

from sqlalchemy import Integer
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    rowid: Mapped[int]  = mapped_column(Integer, system=True)

Upvotes: 1

darc
darc

Reputation: 530

I hope this would help some one, what I found out is that you need to create the table without the rowid column, and then you can simply map the field rowid to a column in the object.

*if you call Base.Metadata.create_all(engine) the column rowid will be created in the table, this will disable sqlite rowid generation.
* didn't check what happen if the primary key is Integer, I think this will override the rowid.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import *

# create the table
metadata = MetaData()
user = Table('users', metadata,
             Column('name', String, primary_key=True),
             Column('fullname', String))

engine = create_engine('sqlite:///example.db')
metadata.create_all(engine)

# create the user object
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'

    name = Column(String, primary_key=True)
    fullname = Column(String)
    rowid = Column(Integer)

    def __repr__(self):
        return "User: name='%s', fullname='%s'" % (self.name, self.fullname)

# insert users
Session = sessionmaker()
Session.configure(bind=engine)
u1 = User(name='ed', fullname='Ed Jones')
u2 = User(name='fred', fullname='Fred Jones')
u3 = User(name='ted', fullname='Ted Jones', rowid=99)

session = Session()
session.add_all([u1, u2, u3])
session.commit()

# read users
users = session.query(User).all()
for user in users:
    print(user, ", rowid=", user.rowid)

The output: 

User: name='ed', fullname='Ed Jones' , rowid= 1   
User: name='fred',fullname='Fred Jones' , rowid= 2   
User: name='ted', fullname='Ted Jones' , rowid= 99

Upvotes: 1

Related Questions