How to get the database row id in a SQLObject Python class method to mimic AddRemoveName

Question

When creating a table with SQLObject, it generates a primary key ID that is not in the Class definition ( ex Person and Address) This ID must be used to point to a related table ( foreign key) As far as i know (beginner for SQLObject i see no way to get that info in a classmethod ( addAddress in the Person class) Is it possible to get the ID without explicit GET.

I include the source code (based on example in SQLOject) to clarify:

from sqlobject import *
import sqlite3
import os

from sqlobject import *
db_filename =os.path.abspath('test.db')
connection_string = 'sqlite:' + db_filename
connection = connectionForURI(connection_string)
sqlhub.processConnection = connection

class Person(SQLObject):
    firstName = StringCol()
    lastName = StringCol()
    addresses = MultipleJoin('Address')

    # instead of passing the parameter id, is it possible
    # to obtain the id of the database record
    # the other info from thta class is accesible ( see print name)

    @classmethod
    def addAddress(cls,address,id):
        # i added id as aparameter but i would like to avoid that.
        # is it possible to have the following
        # data (no error but reference only ?)
        print(cls.firstName)
        # for id it generates an error
        # error Person has no attribute id
        # print(cls.id)
        Address(street = address.street
                ,city=address.city
                ,state=address.state
                ,zip=address.zip
                ,person=id)


Person.createTable(ifNotExists=True)

class Address(SQLObject):

    street = StringCol()
    city = StringCol()
    state = StringCol(length=2)
    zip = StringCol(length=9)
    person = ForeignKey('Person')
    
Address.createTable(ifNotExists=True)

Person(firstName="John", lastName="Doe")

p = Person.get(1)

a=Address(street='123 W Main St', city='Smallsville',
    state='MN', zip='55407', person=p)
p.addAddress(a,p.id)

Does https://stackoverflow.com/a/76912683/7976758 answers your question? — phd, Aug 17 '23 at 11:12

score 1 · Accepted Answer · answered Aug 16 '23 at 10:25

To avoid using "p = Person.get(1)"...

You can change the addAddress method: do not pass the id but retrieve the rowid using a SELECT based on other attributes of the Person class.

getOne() is useful to obtain the single Person instance that matches the specified criteria.
Note that it assumes that there is exactly one matching result. In case of none or multiple results, it will raise an "SQLObjectIntegrityError" exception.

from sqlobject import *
import os

db_filename = os.path.abspath('test.db')
connection_string = 'sqlite:' + db_filename
connection = connectionForURI(connection_string)
sqlhub.processConnection = connection

class Person(SQLObject):
    firstName = StringCol()
    lastName = StringCol()
    addresses = MultipleJoin('Address')

    @classmethod
    def addAddress(cls, address):
        ## Changes here!
        person = cls.selectBy(firstName=address.person.firstName,  
        lastName=address.person.lastName).getOne()    
        Address(street=address.street, city=address.city,  
        state=address.state, zip=address.zip, person=person.id)

Person.createTable(ifNotExists=True)

class Address(SQLObject):
    street = StringCol()
    city = StringCol()
    state = StringCol(length=2)
    zip = StringCol(length=9)
    person = ForeignKey('Person')

Address.createTable(ifNotExists=True)

p = Person(firstName="John", lastName="Doe")
a = Address(street='123 W Main St', city='Smallsville', state='MN', zip='55407', person=p)

Moreover, the last line should be removed.

Person.addAddress(a)

It is unnecessary and would result in an extra identical row in the Address table.
In fact, when you create a new Address instance the SQLObject automatically associates the address with the Person instance p.

Hope is what you wanted, Bye!

How to get the database row id in a SQLObject Python class method to mimic AddRemoveName

1 Answers1