Hi,

I found that my password include %5D and it failed to login. And the same password I use psycopg2 library is OK.

It's some wrong my code or anything i ignored?

Thanks.

Python script runs fine but not by windows task scheduler. It exits with (0x1) in task scheduler. try the following script:

import logging import pandas as pd import sqlalchemy import pprint #import queries from urllib.request import Request, urlopen import datetime

logging.basicConfig(filename='./logs/mylog.log', filemode='a', format='%(asctime)s.%(msecs)03d %(name)s %(levelname)s %(message)s', datefmt='%Y-%m-%d,%H:%M:%S', level=logging.INFO)

logging.info("===== process started =====")

Installing queries via pip install psycopg2 as a dependency. However on many platforms now psycopg2-binary package must be used because of lack of PG dev packages available. For instance, AWS Lambda, Azure Functions etc.

Therefore I believe that either psycopg2 should be removed as a direct dependency and let the user manually add the approprate package or offer a queries-binary variant.

When, for example, an insert without return is executed, attempting to access the queries. Result object's rows raises a psycopg2.ProgrammingError with a message of 'no results to fetch'. The existing check in queries is based on cursor.rowcount and thus fails in this example as rowcount contains the number of rows affected - not necessarily returned.

This commit adds handling of said exception and matches the behaviors if the rowcount were 0.

Can be replicated by accessing the queries.Result object for an insert that inserts one or more rows without returning any results.

Example:

>>> result = session.query('insert into some_table (id) values (123)')
>>> list(result)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/mnt/storage1/queries/queries/results.py", line 48, in __iter__
    for row in self.cursor:
  File "/mnt/storage1/queries/env/lib/python3.6/site-packages/psycopg2-2.7.5-py3.6-linux-x86_64.egg/psycopg2/extras.py", line 117, in __iter__
    first = next(res)
psycopg2.ProgrammingError: no results to fetch

Handling of the error should probably be abstracted.

If an error message is returned from the server during the connecting phase, the socket will remain open after the error has been raised. The socket will continue to be polled forever, causing excessive CPU use.

I am using the TornadoSession with hundreds of queries and if somehow a result isn't freed, tracking it down is a nightmare. I put this together and was wondering if it would be useful to have in the core of the application (if there is a better way please let me know)

import queries


class MySession(queries.TornadoSession):

    def query(self, sql, parameters=None):
        self.sql = sql
        self.parameters = parameters

        return super(MySession, self).query(sql=sql, parameters=parameters)

    def info(self):
        info = []

        for pid, pool in self._pool_manager._pools.items():
            i = pool.idle_connections
            b = [pool.connections[k] for k in pool.connections
                 if pool.connections[k].busy]
            u = [pool.connections[k].used_by().sql for k in
                 pool.connections if pool.connections[k].busy]

            info.append({
                'pid': pid,
                'idle': len(i),
                'busy': len(b),
                'running_queries': u
            })

        return info

If you ever wanted info about the session, you'd just call session.info()

I have seen the concurrent queries example by wrapping the sql statements in a list but that only works if you know beforehand how many queries you are going to send. Is there a method to executemany such as i would do below?

`cars = ( (1, 'Audi', 52642), (2, 'Mercedes', 57127), (3, 'Skoda', 9000), (4, 'Volvo', 29000), (5, 'Bentley', 350000), (6, 'Citroen', 21000), (7, 'Hummer', 41400), (8, 'Volkswagen', 21600) )

cur.executemany(query, cars)`

Thanks in advance!

Maybe I am doing something wrong, but if I were to simply wait until later in the execution stack to call close on a tornado result, it never closes.

conn = queries.queries.TornadoSession(pool_max_size=3)
....
res1 = yield conn.result('some query')
res2 = yield conn.result('some query')
res3 = yield conn.result('some query')

res1.free()
res2.free()
res3.free()

res4 = yield conn.result('some query')
# PoolFullError: Pool <queries.pool.Pool object at $ID> is at its maximum capacity
res4.free()

If I were to free a result before using the connection again, things work fine. (I even tested with a counter by inspecting the conn_pool_manager._pools dict)

Am I missing something?

Added an ability to iterate on empty query results. And a couple of, probably, incorrect tests for this condition.

Previously it was causing psycopg2.ProgrammingError: scroll destination out of bounds. One could overcome this by checking for truthy result before iterating, but I'm not keen.

See https://github.com/psycopg/psycopg2/issues/854, the current requirements of psycopg2>=2.5.1,<2.8 attempts to install psycopg2-2.7.7, but this version is incompatible with python 3.8 due to the above issue, which was fixed in 2.8

in : https://queries.readthedocs.io/en/2.0.0/

the link to "Module Index" (https://queries.readthedocs.io/en/2.0.0/py-modindex.html) is broken.

I'll start with the question as I am not entirely certain if it is even possible; Is there a way to create a queries.Session using a connection_factory? And if not, would it be possible to add such support?

Or.... perhaps there is another way to accomplish the following...?

In cases where I wanted to use a cursor which supported both a NamedTuple result and also using a LoggingCursor I would combine the two cursors but I would also pass in the connection_factory which sets up the connection to utilize logging for the cursor. Is there some way of doing this in queries?

from psycopg2.extras import NamedTupleCursor, LoggingCursor, LoggingConnection


class MyLoggingCursor(LoggingCursor):
    def execute(self, query, vars=None):
        self.timestamp = time.time()
        return super(MyLoggingCursor, self).execute(query, vars)

    def callproc(self, procname, vars=None):
        self.timestamp = time.time()
        return super(MyLoggingCursor, self).callproc(procname, vars)


class MyLoggingConnection(LoggingConnection):
    def filter(self, msg, curs):
        duration = int((time.time() - curs.timestamp) * 1000)
        output = f"{msg}  ==> {curs.rowcount} rows, {duration:d} ms"
        return output

    def cursor(self, *args, **kwargs):
        kwargs.setdefault('cursor_factory', MixinLoggedNamedTupleCursor)
        return LoggingConnection.cursor(self, *args, **kwargs)


class MixinLoggedNamedTupleCursor(MyLoggingCursor, NamedTupleCursor):
    pass


db_conn = psycopg2.connect(host=db_host, port=db_port,
                           user=db_user, password=db_pass,
                           database=db_name, 
                           connect_timeout=timeout,
                           connection_factory=MyLoggingConnection
                           )
db_conn.initialize(logger)

Most simple example from docs:

for row in session.query('SELECT * FROM foo'):
    print row

Fails when there are no rows in the table:

Traceback (most recent call last):
  File "venv/lib/python3.9/site-packages/queries/results.py", line 45, in __iter__
    raise StopIteration
StopIteration

Expected result: for loop should just pass without errors

When, for example, an insert without return is executed, attempting to access the queries.Result object's rows raises a psycopg2.ProgrammingError with a message of 'no results to fetch'. The existing check in queries is based on cursor.rowcount and thus fails in this example as rowcount contains the number of rows affected - not necessarily returned.

This commit adds handling of said exception and matches the behaviors if the rowcount were 0.

Hello ! First of all thank you for the work you put in your lib !

We spotted a strange behavior when using queries.TornadoSession in a context manager: The connection pool is not freed when exiting the with block. (This isn't the case for queries.Session).

It is true that the documentation encourages to use result.free() when working with asynchronous sessions but IMHO it would be a nice feature if both Session classes would behave the same.

Cheers !