Pony ORM - Resolve 'Expected string or bytes-like object' error

Question

I'm currently developing an API for AWS with Chalice in Python that uses Pony ORM to handle our database. When trying to query with a select like this db.select(s.start_time for s in db.Session) I'm getting the 'Expected string or bytes-like object" error (full stack-trace below). However querying using a lambda like this db.Session.select(lambda s: s.id = 3) works as expected. I'm at a loss to what could be causing it, a guess would be that the db.Provider part isn't liked when generating, but I'm not sure what Pony expects there. I've tried debugging with pdb, but I'm not sure what it's telling me.

Stack trace:

Traceback (most recent call last): File "c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\chalice\app.py", line 842, in _get_view_function_response response = view_function(**function_args) File "C:\Users\Gamer\Documents\AWS-SakMed\backend\SakMed\app.py", line 51, in _view_function return wrapped(*args, **kwargs) File "", line 2, in get_cases File "c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py", line 528, in new_func result = func(*args, **kwargs) File "C:\Users\Gamer\Documents\AWS-SakMed\backend\SakMed\app.py", line 89, in get_cases query = db.select(p.first_name for p in db.Provider) File "c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py", line 881, in select if not select_re.match(sql): sql = 'select ' + sql TypeError: expected string or bytes-like object

Stepping through pdb debug (formatting is a bit weird):

c:\users\gamer\documents\aws-sakmed\backend\sakmed\app.py(89)get_cases()

-> query = db.select(p.first_name for p in db.Provider) (Pdb) step(s)
--Call-- c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(3927)iter()

-> def iter(entity): (Pdb) c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(3928)iter()

-> return EntityIter(entity) (Pdb)
--Call-- c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(3630)init()

-> def init(self, entity): (Pdb) c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(3631)init()

-> self.entity = entity (Pdb)
--Return-- c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(3631)init()->None

-> self.entity = entity (Pdb)
--Return-- c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(3928)iter()->
-> return EntityIter(entity) (Pdb)
--Call-- c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(879)select()

-> @cut_traceback (Pdb) c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(881)select()

-> if not select_re.match(sql): sql = 'select ' + sql (Pdb) pp(sql) generator object get_cases.locals.genexpr at 0x048062B0 (Pdb)
step(s) TypeError: expected string or bytes-like object c:\users\gamer.virtualenvs\backend-qptpobgm\lib\site-packages\pony\orm\core.py(881)select() -> if not select_re.match(sql): sql = 'select ' + sql (Pdb)

Relevant code: app.py

db = create_database()
datastore = DataStore(db)

def app_db_session(func):
    wrapped = db_session(func)
    def _view_function(*args, **kwargs):
        global db_is_bound
        if not db_is_bound:
            debug = os.getenv('localdev')
            if debug is None:
                datastore.connect(host, name, password, dbname)
            elif debug == 'True':
                datastore.connect('localhost', 'user', 'password', 'local-db')
            db_is_bound = True
        return wrapped(*args, **kwargs)
    return _view_function

@app.route('/recipient/{rec_id}/cases', methods=['GET'])
@app_db_session
def get_cases(rec_id):

    query = db.Provider.select(lambda p: p.id == 1)
    query = db.select(p.first_name for p in db.Provider))

Relevant code: data_store.py

class DataStore():
    def __init__(self, db):
        self.db = db
    def connect(self, host, user, passwd, db_name):
        self.db.bind(provider='mysql', host=host, user=user, passwd=passwd, db=db_name)
        self.__map_data_models()
    def bind_memory(self):
        self.db.bind(provider='sqlite', filename=':memory:')
        self.__map_data_models()
    def __map_data_models(self):
        self.db.generate_mapping(create_tables=True)

Relevant code: base.py

def create_database():

    db = Database()

    class Provider(db.Entity):
        id = PrimaryKey(int, auto=True)
        hsa_id = Required(str)
        role = Optional(str)
        available = Required(bool)
        first_name = Optional(str)
        last_name = Optional(str)
    return db

Answer 1

If you want to use generator syntax, you need to use select function:

from pony import orm
...
query = orm.select(p for p in Person if p.name.startswith('A'))
for obj in query:
    print(obj.name)

The method select of Database object is used for raw SQL queries

from pony import orm
...
db = orm.Database('sqlite', ':memory:')
...
rows = db.select("id, name FROM person p WHERE p.name LIKE 'A%'")
for row in rows:
    print(row[1])