PostgreSQL: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) invalid sslmode value: "fastapi"

Question

Hi I am trying PostgreSQL but getting this error:

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) invalid sslmode value: "fastapi"

I am following a tutorial ( https://www.tutlinks.com/fastapi-with-postgresql-crud-async/ ) My main.py is:

from fastapi import FastAPI, applications
from fastapi.middleware.cors import CORSMiddleware
from typing import List
import urllib, os, sqlalchemy, databases

from sqlalchemy import engine
from sqlalchemy.sql.expression import text

host_server = os.environ.get('host_server', 'localhost')
db_server_port = urllib.parse.quote_plus(str(os.environ.get('db_server_port', '5432')))
database_name = os.environ.get('database_name', 'fastapi')
db_username = urllib.parse.quote_plus(str(os.environ.get('db_username','postgres')))
db_password = urllib.parse.quote_plus(str(os.environ.get('db_password','secret')))
ssl_mode = urllib.parse.quote_plus(str(os.environ.get('ssl_mode','prefer')))
DATABASE_URL = 'postgresql://{}:{}@{}:{}?sslmode={}'.format(db_username, db_password, host_server, db_server_port, database_name, ssl_mode)

metadata = sqlalchemy.MetaData()

notes = sqlalchemy.Table(
    "notes",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("text", sqlalchemy.String),
    sqlalchemy.Column("completed", sqlalchemy.Boolean),
)

engine = sqlalchemy.create_engine(
    #DATABASE_URL
    DATABASE_URL, pool_size=3, max_overflow=0
)

metadata.create_all(engine)

from pydantic import BaseModel

class NoteIn(BaseModel):
    text: str
    completed: bool

class Note(BaseModel):
    id: int
    text: str
    completed: bool

app = FastAPI(title="Rest API using FastAPI PostgreSQL Async Endpoints")
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"]
)

#import databases

database = databases.Database(DATABASE_URL)

@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

@app.post("/notes/", response_model= Note)
async def create_note(note: NoteIn):
    query = notes.insert().values(text= note.text, completed = note.completed)
    last_record_id = await database.execute(query)
    return {**note.dict(), "id": last_record_id}

and error message:

WARNING:  StatReload detected file change in 'main.py'. Reloading...
Process SpawnProcess-3:
Traceback (most recent call last):
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 307, in connect
    return _ConnectionFairy._checkout(self)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 767, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 425, in checkout
    rec = pool._do_get()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
    self._dec_overflow()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
    return self._create_connection()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 253, in _create_connection
    return _ConnectionRecord(self)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 368, in __init__
    self.__connect()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 611, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 605, in __connect
    connection = pool._invoke_creator(self)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: invalid sslmode value: "fastapi"


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\learn\AppData\Local\Programs\Python\Python39\lib\multiprocessing\process.py", line 315, in _bootstrap
    self.run()
  File "C:\Users\learn\AppData\Local\Programs\Python\Python39\lib\multiprocessing\process.py", line 108, in run
    self._target(*self._args, **self._kwargs)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\uvicorn\subprocess.py", line 76, in subprocess_started
    target(sockets=sockets)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\uvicorn\server.py", line 68, in run
    return asyncio.run(self.serve(sockets=sockets))
  File "C:\Users\learn\AppData\Local\Programs\Python\Python39\lib\asyncio\runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "C:\Users\learn\AppData\Local\Programs\Python\Python39\lib\asyncio\base_events.py", line 642, in run_until_complete
    return future.result()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\uvicorn\server.py", line 76, in serve
    config.load()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\uvicorn\config.py", line 448, in load
    self.loaded_app = import_from_string(self.app)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\uvicorn\importer.py", line 21, in import_from_string
    module = importlib.import_module(module_str)
  File "C:\Users\learn\AppData\Local\Programs\Python\Python39\lib\importlib\__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1030, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1007, in _find_and_load
  File "<frozen importlib._bootstrap>", line 986, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 680, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 850, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "F:\fastapi-codes\psgresql\.\main.py", line 32, in <module>
    metadata.create_all(engine)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\sql\schema.py", line 4740, in create_all
    bind._run_ddl_visitor(
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 3078, in _run_ddl_visitor
    with self.begin() as conn:
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 2994, in begin
    conn = self.connect(close_with_result=close_with_result)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 3166, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
    else engine.raw_connection()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 3245, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 3215, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 2069, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 307, in connect
    return _ConnectionFairy._checkout(self)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 767, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\base.py", line 425, in checkout
    rec = pool._do_get()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
    self._dec_overflow()
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
nect
    return dialect.connect(*cargs, **cparams)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\sqlalchemy\engine\default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "f:\fastapi-codes\psgresql\env\lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) invalid sslmode value: "fastapi"

(Background on this error at: https://sqlalche.me/e/14/e3q8)
INFO:     Stopping reloader process [2724]

I have created database 'fastapi'in pgAdmin4 and it shows connected message. I am also getting error in 'import databases' though I have installed databases==0.5.0 from requirements.txt

What am I doing incorrect?

jjanes · Accepted Answer · 2021-09-05 15:29:20Z

1

Your URL constructor has 5 placeholders, but 6 parameters. You need a placeholder for the database name parameter.

answered Sep 5, 2021 at 15:29

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

Comments

Stefan · Accepted Answer · 2022-03-31 12:01:37Z

0

Use this:

DATABASE_URL = 'postgresql://{}:{}@{}:{}/{}?sslmode={}'.format(db_username, db_password, host_server, db_server_port, database_name, ssl_mode)

edited Mar 31, 2022 at 12:01

Stefan

1,96222 silver badges38 bronze badges

answered Mar 31, 2022 at 7:43

Satyam

314 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) invalid sslmode value: "fastapi"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related