What are the valid `query` keys for `sqlalchemy.engine.URL.create` that work for accessing a PostgreSQL database?

user_4348400 0
Viewed 58

I am interested in using the sqlalchemy.engine.URL.create constructor, but I am finding the documentation to be a too brief on the details to understand how to use it.

Here are the parameters:

  • drivername: the name of the database backend. This name will correspond to a module in sqlalchemy/databases or a third party plug-in.
  • username: The user name
  • password: database password. Is typically a string, but may also be an object that can be stringified with str().
  • host: The name of the host
  • port: The port number
  • database: The database name
  • query: A dictionary of string keys to string values to be passed to the dialect and/or the DBAPI upon connect. To specify non-string parameters to a Python DBAPI directly, use the create_engine.connect_args parameter to create_engine(). See also URL.normalized_query for a dictionary that is consistently string->list of string.

The parameter I am confused about is query. I don't know how to use it for PostgreSQL. I found an example that works for Microsoft SQL:

from sqlalchemy.engine import URL
from sqlalchemy import create_engine

# CONFIG
SERVER_NAME = 'FOO'
DATABASE_NAME = 'BAR'

# QUERY
QUERY = '''SELECT * FROM table'''

# CONNECTION
CONNECTION_STRING = 'Driver={SQL Server};Server={%s};Database=%s;Trusted_Connection=yes;' % (
    SERVER_NAME,
    DATABASE_NAME
    )

CONNECTION_URL = URL.create("mssql+pyodbc",
 query={"odbc_connect": CONNECTION_STRING})


CONNECTION = create_engine(CONNECTION_URL)

Which makes it evident that I can use it for passing in the connection string. I think I can replace "mssql+pyodbc" with "postgresql+psycopg2", but I don't know what to replace "odbc_connect" with.

  1. (short-term) What can I put into query to change to PostgreSQL and
  2. (more fundamentally) where can I find detailed documentation for understanding the possible inputs of the query parameter?
sqlalchemy postgresql python
1 Answers

query= is how we specify anything that follows ? in the string version of the connection URL. (The values following ? have long been known as the "querystring" in a URL.)

odbc_connect is a special case for ODBC connections (e.g., mssql+pyodbc://). There is no equivalent for PostgreSQL because none of the supported drivers for the postgresql dialect use ODBC. So the answer to

What can I put into query to change to PostgreSQL

is "Nothing. That is not an option."

As for things you can put into query for PostgreSQL, any of the additional key=value listed in the psycopg2 driver documentation could be used. For example, if you wanted to create an Engine that uses database connections where SSL is required you would use something like

connection_url = URL.create(
    "postgresql+psycopg2",
    username="scott",
    password="tiger",
    host="192.168.0.199",
    database="test",
    query={"sslmode": "require"}
)
print(connection_url)
# postgresql+psycopg2://scott:tiger@192.168.0.199/test?sslmode=require
user_21443900
Related