0

I'm trying to use sqlaclhemy to create an engine using create_engin() method. Before the ssl I was using the command:

conn_string = r'postgresql://root:my_pass@hostname/db_name'
self.engine = create_engine(conn_string)

But after starting to use ssl this stopped working. I tried looking for similar problems and found How do I connect to Postgresql using SSL from SqlAchemy+pg8000?

But when I try to use the answer self.engine = create_engine(conn_string, connect_args={'sslmode': 'require'}) I get an error

password authentication failed for user "root"

Even though my password is correct. I was able to connect using psychopg2 api as follows:

conn = psycopg2.connect(dbname='postgres', user='root', password=my_pass,
                     host=host_name, port='5432', sslmode='require')

This works for me, but it is not good because I have my own api that uses sqlalchemy engine and I don't want to break it. I have tried to find a way to maybe create an engine using the conn created above, but the only thing I could find is Create a sqlalchemy engine using an existing psycopg2 connection pool, and I don't need a thread pool, nor does the psycopg2 has a .pool method anyways

How is it possible that the psycopg2 succeed where the sqlalchemy fails?

Dvir Itzko
  • 404
  • 4
  • 17

1 Answers1

1

So apparently the problem was with the password. When passing connect_args argument, if you have some special characters [for me it was %] in the password, then sqlalchemy parse the string badly, thus you will get a password authentication failed for user "root" error

[for example for the following password won't work

kltgg2dds$fdslkj%3

but for a password without the % it will work]

Dvir Itzko
  • 404
  • 4
  • 17
  • One has to URL encode the stuff, see the answer linked as duplicate above. I always read answers before the question so I missed the linked answer above and thus I added this comment for future searchers. Take care! – bugmenot123 Mar 04 '20 at 10:56