can I get data by execute raw sql

[gensword]

Is your feature request related to a problem? Please describe. A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like A clear and concise description of what you want to happen.

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

Additional context Add any other context about the feature request here.

Sometimes there maby complex sqls. I want to execute raw sqls to fetch results. Does tortoise provide a api to do it? thanks

[grigi]

We have an unofficial api e.g:

async with in_transaction() as conn:
    list_of_dicts = conn.execute_query(
        "select some, rows from some_table where condition = ?", 
        ['value']
    )

The problem is that it isn't portable between DB's. e.g. you have to use the variable containers of what the db connector expects. ? → SQLite %s → MySQL &1 &2 … &n → PostgreSQL

I'm sure you would want a formal API that abstracs at least the query parameters away for you?

[gensword]

Thanks about the solution above.Maybe a good way is both provide low-level api(at least keep people away from sql engine prams) and high-level api to query raw sqls for complex querys.

def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()

    return row

query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
Person.objects.raw(query)

Maybe Django orm above is a good reference. By the way tortoise-orm is awesome.

[grigi]

Thank you :smile:

Yes, it makes sense. I'm thinking of low-level (that handles SQL params) and a high-level that allows usage of PyPika queries directly. :thinking: That may actually make parametrising all the remaining things a bit easier…

[grigi]

@gensword Could you comment on the plan in #139 ? That is only talking about one of the two parts mentioned here.

[mojimi]

@grigi The snippet you posted did not work for me, I had to add the await keyword for it to go through.

async with in_transaction() as connection:
        await connection.execute_query('drop schema public cascade')
        await connection.execute_query('create schema public')

Is this as intended?

[grigi]

Yes, the async with is correct.

[Iamsdt]

I am trying to use the same in_transaction with PostgreSQL, but always getting this error

tortoise.exceptions.OperationalError: operator does not exist: & integer
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

here is my code, can you tell me what is the fix here Note: here episode_card is pgview

async with in_transaction() as trans:
    data2 = await trans.execute_query("""
        SELECT * FROM public.episode_card where title = &1""", ["hello"])

[roks0n]

To anyone coming here to look for Postgresql; there's a typo in https://github.com/tortoise/tortoise-orm/issues/191#issuecomment-532295132, right syntax is $1, $2, eg:

async with in_transaction() as conn:
    list_of_dicts = conn.execute_query(
        "select some, rows from some_table where condition = $1", 
        ['value']
    )

Cheers to https://github.com/tortoise/tortoise-orm/issues/818#issuecomment-879061879!