CODEWITHSUNDEEP
pythonsqlsqlitepyqt
JPFrancoia
JPFrancoia

Reputation: 5629

Condition in a request

I wonder if there is a way to filter the results given by a request with a function, written in python. Something like that:

SELECT id, name, path IF verifAccess(path)

In my example, verifAcces would be a function I wrote. It returns True if the path is accessible, or False if not.

Thanks.

That's the request I need to filter:

def displayWaitingList(self):

    self.button_waiting.setEnabled(False)
    self.showing_waiting = True

    try:
        self.waiting_list
    except AttributeError:
        return

    self.query = QtSql.QSqlQuery()

    requete = "SELECT * FROM videos WHERE id IN ("

    for each_id in self.waiting_list:
        if self.waiting_list.index(each_id) != len(self.waiting_list) - 1:
            requete = requete + str(each_id) + ", "
        else:
            requete = requete + str(each_id) + ")"

    self.query.prepare(requete)
    self.query.exec_()

    self.modele.setQuery(self.query)

    self.proxy.setSourceModel(self.modele)
    self.tableau.setModel(self.proxy)

Upvotes: 2

Views: 224

Answers (2)

Paulo Scardine
Paulo Scardine

Reputation: 77359

The SQL query is executed in the database server, so if you have a backend supporting Python, you can write stored procedures and functions in Python.

PostgreSQL has PL/Py:

Pure Python: All code, at first, is written in pure Python so that py-postgresql will work anywhere that you can install Python 3. Optimizations in C are made where needed, but are always optional.

Prepared Statements: Using the PG-API interface, protocol-level prepared statements may be created and used multiple times. db.prepare(sql)(*args)

COPY Support: Use the convenient COPY interface to directly copy data from one connection to another. No intermediate files or tricks are necessary.

Arrays and Composite Types: Arrays and composites are fully supported. Queries requesting them will returns objects that provide access to the elements within.

Quick Console: Get a Python console with a connection to PostgreSQL for quick tests and simple scripts.

source: http://python.projects.pgfoundry.org/

You may find the Pony ORM very interesting. It allows querying a database using plain python instead of SQL:

select(c for c in Customer if sum(c.orders.price) > 1000)

The above statement generates the following query:

SELECT "c"."id"
FROM "Customer" "c"
  LEFT JOIN "Order" "order-1"
    ON "c"."id" = "order-1"."customer"
GROUP BY "c"."id"
HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000

[update]

Okay, I'm going to have a look at it. Thanks. But nothing native ? – user1585507

By native you mean "using the core library only"? No, there isn't. If you can't use PL/Py, your best shot is an ORM like SQLAlchemy (that is very expressive in the SQL side), or one like Pony (that is more expressive in the Python side). Both will let you reuse and composite queries easily.

If you are letting the user construct complex query conditions, and trying to avoid the misery of composing SQL queries using string interpolation and concatenation, I recommend SQLAlchemy core.

If your queries are simple and you just want avoid the impedance mismatch between Python and SQL as much as you can, then use Pony.

Upvotes: 2

roman
roman

Reputation: 117540

You can filter it on the client side:

Create tables and populate with data:

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('create table test(id int, name text)')
conn.execute("insert into test(id, name) select 1, 'test'")
conn.execute("insert into test(id, name) select 2, 'test2'")

def verifAccess(id):
    return id == 1

Quering:

>>> [x for x in conn.execute('select id, name from test')]
[(1, u'test'), (2, u'test2')]
>>> [x for x in conn.execute('select id, name from test') if verifAccess(x[0])]
[(1, u'test')]

You could write Python functions in PostgreSQL, but it should be function created on your server and it's not very efficient way to filter data from the table - indexes will not be used.

Upvotes: 0

Related Questions