Connection error

[Ryan Bandler]

Hello everyone,

I am a first-time SQLalchemy user planning on using SQLalchemy on a new project. We already have an established postgres database (currently still on localhost) which I do not want to handwrite the SQLalchemy model for. So I am planning to use sqlacodegen. Unfortunately, sqlacodegen is giving me:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused

        Is the server running on host "localhost" (127.0.0.1) and accepting

        TCP/IP connections on port 5432?

My systems at work are very restrictive, but I was able to gain permissions to the postgres config files and edited them to allow all TCP connections. I restarted the postgres service and I am still experiencing this error. I dont understand why this is happening, because it seems to be an error coming from psycopg2 being called in sqlalchemy, and i have ever had any issues connecting to the DB with psycopg2 before. 

If anyone has any experience with sqlacodegen, any help would be much appreciated!!

[Rich Shepard]

On Tue, 15 Jun 2021, Ryan Bandler wrote:

> I am a first-time SQLalchemy user planning on using SQLalchemy on a new
> project.

Ryan,

I started to learn SA long ago, but put off the project. Now I'm starting
over again with much to learn.

> We already have an established postgres database (currently still on
> localhost) which I do not want to handwrite the SQLalchemy model for. So I
> am planning to use sqlacodegen. Unfortunately, sqlacodegen is giving me:

My postgres databases exist and have data. I used the short version of
declarative models; don't know if the concept and syntax still holds for SA
version 1.4.x

For one project the model.py file is:
"""
This is the SQLAlchemy declarative mapping python classes to postgres
tables for the business tracker.
"""

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Unicode, Integer, String, Date
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import CheckConstraint
from sqlalchemy.orm import Session
from sqlalchemy.dialects import postgresql

"""Base = declarative_base()"""
Base = automap_base()

engine = create_engine('postgresql+psycopg2:///bustrac')

# reflect the tables
Base.prepare(engine, reflect=True)

State = postgresql.Enum('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY', 'AB', 'BC', 'MB', 'NB', 'NL', 'NT', 'NS', 'NU', 'ON', 'PE', 'QC', 'SK', 'YT', name='states')

Industries = Base.classes.industries
Status = Base.classes.status
StatusTypes = Base.classes.statusTypes
ActivityTypes = Base.classes.activityTypes
Organizations = Base.classes.organizations
Locations = Base.classes.locations
People = Base.classes.people
Activities = Base.classes.activities
Projects = Base.classes.projects

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

HTH,

Rich

[Ryan Bandler]

Hi Rich,

I appreciate the response. I understand how the structure of the SA model works, it really is quite simple. My question was about an error while using a third party tool called sqlacodegen, which is a tool which connects to a DB and automatically generates the SA model. I am using this tool because I have many tables with lots of columns and I dont like typing boilerplate code when I dont have to. My question was not about SA itself or how writing the model works ,but about this error I am experiencing with this generator tool. I appreciate the response though!

Best,

Ryan

[jonatha...@gmail.com]

Have you confirmed that you can connect to 127.0.0.1 at port 5432 using psql? On my development system, I normally use a local (UNIX domain) socket, which is libpq's default behavior. When I run "psql -h 127.0.0.1", I get the following error:

psql: could not connect to server: Connection refused
    Is the server running on host "127.0.0.1" and accepting

    TCP/IP connections on port 5432?

[Ryan Bandler]

Yeah I connect to the DB over localhost:5432 via psql all the time and also haven't had any issues connecting via psycopg2. I also have no issue connecting to the database in sqlalchem, it's only an issue with sqlacodegen.

[Ryan Bandler]

Anyways guys I was able to fix the issue by installing a flask-specific version of the same package (even though I am not using flask) and I was able to get it to work just fine. Thanks everyone for your help.

[Rich Shepard]

On Wed, 16 Jun 2021, Ryan Bandler wrote:

> Yeah I connect to the DB over localhost:5432 via psql all the time and
> also haven't had any issues connecting via psycopg2. I also have no issue
> connecting to the database in sqlalchem, it's only an issue with
> sqlacodegen.

Ryan,

Just to confirm, you're running sqlacodegen from the OS shell and not from
the python REPL, correct?

Rich