I am trying to load pandas Dataframe into Redshift table using below code.
#PYTHON 3.8#
import pandas as pd
import sqlalchemy as sq
url = sq.engine.url.URL.create(
drivername='redshift+redshift_connector', # indicate
redshift_connector driver and dialect will be used
host=f"{redshift_endpoint}", # Amazon Redshift host
port=5439, # Amazon Redshift port
database='dev', # Amazon Redshift database
username=f"{redshift_db_user}", # Amazon Redshift username
password=f"{redshift_db_password}" # Amazon Redshift password
)
print('Connection URL is',url)
engine=sq.create_engine(url)
cnn = engine.connect()
I am getting below error while creation of engine.
>[ERROR] AttributeError: module 'sqlalchemy.util' has no attribute 'text_type'
>>Traceback (most recent call last):
File "/var/task/lambda_function.py", line 44, in lambda_handler
cnn = engine.connect()
File "/opt/python/sqlalchemy/engine/base.py", line 3268, in connect
return self._connection_cls(self)
File "/opt/python/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "/opt/python/sqlalchemy/engine/base.py", line 3292, in raw_connection
return self.pool.connect()
File "/opt/python/sqlalchemy/pool/base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
File "/opt/python/sqlalchemy/pool/base.py", line 1269, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/opt/python/sqlalchemy/pool/base.py", line 716, in checkout
rec = pool._do_get()
File "/opt/python/sqlalchemy/pool/impl.py", line 170, in _do_get
self._dec_overflow()
File "/opt/python/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/opt/python/sqlalchemy/pool/impl.py", line 167, in _do_get
return self._create_connection()
File "/opt/python/sqlalchemy/pool/base.py", line 393, in _create_connection
return _ConnectionRecord(self)
File "/opt/python/sqlalchemy/pool/base.py", line 678, in __init__
self.__connect()
File "/opt/python/sqlalchemy/pool/base.py", line 914, in __connect
pool.dispatch.connect.for_modify(
File "/opt/python/sqlalchemy/event/attr.py", line 473, in _exec_w_sync_on_first_run
self(*args, **kw)
File "/opt/python/sqlalchemy/event/attr.py", line 487, in __call__
fn(*args, **kw)
File "/opt/python/sqlalchemy/engine/create.py", line 704, in on_connect
do_on_connect(dbapi_connection)
File "/opt/python/sqlalchemy_redshift/dialect.py", line 1395, in on_connect
fn(conn)
File "/opt/python/sqlalchemy_redshift/dialect.py", line 1373, in on_connect
conn.py_types[quoted_name] = conn.py_types[util.text_type]
I have used SQLalchmy and redshift_connector for creating connection. Please let me know what am I doing incorrectly.
The issue was caused due version compatibility between sqlalchemy_redshift and sqlalchemy. I had installed version 2.0.23 of sqlalchemy but sqlalchemy_redshift uses version 1.4.50. We do not have to explicitly install sqlalchemy because it's compatible version gets installed with sqlalchemy_redshift as a dependency.