How to generate SQL query for postgres (Redshift) without connection?

Question

I want to build SQL query to pass into spark-redshift reader's "query" option. I'm trying to use psycopg2, so I do something like this:

from psycopg2 import sql

query = sql.SQL(
    "select * from {} where event_timestamp < {}"
).format(
    sql.Identifier("events"),
    sql.Literal(datetime.now())
).as_string()

But it tells me that I need to pass context (connection or cursor) to as_string(). I'm not able to, because I don't have any connection.

Should I use plain string format in this case with some escaping?

Or is there any way to pass some mock context there? And why it needs connection to build query string? Does SQL query change depending on connection?

MikeTwo · Accepted Answer · 2017-10-21 18:05:22Z

1

I'm not familiar with spark, but I'd be surprised if they didn't have some kind of sql support. Another alternative is a lightweight package like sqlbuilder.

If you really want to use psycopg, I suggest looking at how they unit test with mocks -- psycopg2's ConnectingTestCase.

class ConnectingTestCase(unittest.TestCase):
    """A test case providing connections for tests.

    A connection for the test is always available as `self.conn`. Others can be
    created with `self.connect()`. All are closed on tearDown.

    Subclasses needing to customize setUp and tearDown should remember to call
    the base class implementations.
    """

answered Oct 21, 2017 at 18:05

MikeTwo

1,3751 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mossab · Accepted Answer · 2025-08-05 10:42:30Z

0

A connection isn't required in psycopg v3

uninstall psycopg2
```
pip uninstall psycopg2 psycopg2-binary
```
install latest
```
pip install "psycopg[binary]"
```

Do not pass connection

import psycopg
from psycopg import sql

function_name = sql.Identifier('my_function')
args = [sql.Literal(1), sql.Literal('abc'), sql.Literal(True)]

query = sql.SQL("SELECT {}({});").format(
    function_name,
    sql.SQL(', ').join(args)
)


# Print the SQL
print(query.as_string())

answered Aug 5 at 10:42

Mossab

1,14312 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to generate SQL query for postgres (Redshift) without connection?

2 Answers 2

Comments

A connection isn't required in psycopg v3

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

A connection isn't required in psycopg v3

Comments

Your Answer

Sign up or log in

Post as a guest

Related