Connecting to database using sqlalchemy yielding kwargs error

Question

When I run this basic code to access my AWS redshift data warehouse, I get this error:

Traceback (most recent call last):

  File "<ipython-input-40-dcebeb0708d9>", line 25, in <module>
    engine = sqlalchemy.create_engine(URL)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\__init__.py", line 479, in create_engine
    return strategy.create(*args, **kwargs)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\strategies.py", line 56, in create
    plugins = u._instantiate_plugins(kwargs)

TypeError: _instantiate_plugins() missing 1 required positional argument: 'kwargs'

This is the code I'm running. I originally planned on using psycopg2 to access the database but psycopg2 returns the sql query as a list with funky formatting; I'd have to do too much work to extract the data into a dataframe. I'm hoping sqlalchemy produces and output that's easier to work with.

import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
import pandas as pd

from sqlalchemy.engine.url import URL

config = dict(
    drivername='driver',
    username= username_u,
    password= password_u,
    host='rs-data.prod-lde.bsp.gsa.gov',
    port='5439',
    database='edw',
    query={'encoding': 'utf-8'}
)

url = URL(**config)

engine = sqlalchemy.create_engine(url) # Having "URL" instead of "url" caused the original problem

Thanks in advance.

>>>>>Update<<<<<: I fixed my silly mistake and the new code is this:

import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
import pandas as pd
from sqlalchemy.engine.url import URL

config = dict(
    drivername='postgresql',
    username= username_u,
    password= password_u,
    host='rs-data.prod-lde.bsp.gsa.gov',
    port='5439',
    database='edw',
    query={'encoding': 'utf-8'}
)

url = URL(**config)
engine = sqlalchemy.create_engine(url)

data_frame = pd.read_sql('select date_signed from edw.fpds.fpds_atom limit 5;', engine)

Now I'm getting this error:

ProgrammingError: (psycopg2.ProgrammingError) invalid dsn: invalid connection option "encoding"

(Background on this error at: http://sqlalche.me/e/f405)

I think the problem lies in the config dict values. I first had it as drivername='driver' and that caused this error:

Traceback (most recent call last):

  File "<ipython-input-48-dcb4b19c1bd3>", line 25, in <module>
    engine = sqlalchemy.create_engine(url)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\__init__.py", line 479, in create_engine
    return strategy.create(*args, **kwargs)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\strategies.py", line 61, in create
    entrypoint = u._get_entrypoint()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\url.py", line 172, in _get_entrypoint
    cls = registry.load(name)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py", line 240, in load
    "Can't load plugin: %s:%s" % (self.group, name)

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:driver

so I changed it to drivername='postgresql' and that produced this error:

Traceback (most recent call last):

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 363, in connect
    return _ConnectionFairy._checkout(self)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 773, in _checkout
    fairy = _ConnectionRecord.checkout(pool)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\impl.py", line 139, in _do_get
    self._dec_overflow()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
    raise value

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\impl.py", line 136, in _do_get
    return self._create_connection()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 652, in __connect
    connection = pool._invoke_creator(self)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 489, in connect
    return self.dbapi.connect(*cargs, **cparams)

  File "C:\Anaconda3\lib\site-packages\psycopg2\__init__.py", line 126, in connect
    dsn = _ext.make_dsn(dsn, **kwargs)

  File "C:\Anaconda3\lib\site-packages\psycopg2\extensions.py", line 175, in make_dsn
    parse_dsn(dsn)

ProgrammingError: invalid dsn: invalid connection option "encoding"



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

Traceback (most recent call last):

  File "<ipython-input-53-3b57714b0ad8>", line 28, in <module>
    data_frame = pd.read_sql('select date_signed from edw.fpds.fpds_atom limit 5;', engine)

  File "C:\Anaconda3\lib\site-packages\pandas\io\sql.py", line 438, in read_sql
    chunksize=chunksize,

  File "C:\Anaconda3\lib\site-packages\pandas\io\sql.py", line 1218, in read_query
    result = self.execute(*args)

  File "C:\Anaconda3\lib\site-packages\pandas\io\sql.py", line 1087, in execute
    return self.connectable.execute(*args, **kwargs)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2181, in execute
    connection = self._contextual_connect(close_with_result=True)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2242, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2280, in _wrap_pool_connect
    e, dialect, self

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1547, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 363, in connect
    return _ConnectionFairy._checkout(self)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 773, in _checkout
    fairy = _ConnectionRecord.checkout(pool)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\impl.py", line 139, in _do_get
    self._dec_overflow()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
    raise value

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\impl.py", line 136, in _do_get
    return self._create_connection()

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py", line 652, in __connect
    connection = pool._invoke_creator(self)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)

  File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 489, in connect
    return self.dbapi.connect(*cargs, **cparams)

  File "C:\Anaconda3\lib\site-packages\psycopg2\__init__.py", line 126, in connect
    dsn = _ext.make_dsn(dsn, **kwargs)

  File "C:\Anaconda3\lib\site-packages\psycopg2\extensions.py", line 175, in make_dsn
    parse_dsn(dsn)

ProgrammingError: (psycopg2.ProgrammingError) invalid dsn: invalid connection option "encoding"

(Background on this error at: http://sqlalche.me/e/f405)

The key question I guess is what should I be using for the drivername parameter?

You are passing the uppercase `URL` into `create_engine`. Should it be the variable `url` instead? — JD2775, Apr 29 '21 at 16:50
If you remove the encoding: 'utf' stuff from the config dict, the code works — mbs1, Apr 29 '21 at 17:29
Check the last answer on this post: https://stackoverflow.com/questions/45279863/how-to-use-charset-and-encoding-in-create-engine-of-sqlalchemy-to-create — JD2775, Apr 29 '21 at 17:30

Connecting to database using sqlalchemy yielding kwargs error

0 Answers0