How To Run Sqlacodegen?

I'm not understanding why I can't run sqlacodegen. I'm looking to use it for create a SQLAlchemy model from my existing PostgreSQL database. It won't run. When I type sqlacodeg

Solution 1:

It is because you did this in Python shell:

>>> import sqlacodegen
>>> sqlacodegen --help
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: bad operand type for unary -: '_Helper'

You should have executed sqlacodegen --help in your Unix command shell / Windows command prompt:

% sqlacodegen --help
usage: sqlacodegen [-h] [--version] [--schema SCHEMA] [--tables TABLES]
                   [--noviews] [--noindexes] [--noconstraints] [--nojoined]
                   [--noinflect] [--outfile OUTFILE]
                   [url]

Generates SQLAlchemy model code from an existing database.

positional arguments:
  url                SQLAlchemy url to the database

optional arguments:
  -h, --help         show this help message and exit
  --version          print the version number and exit
  --schema SCHEMA    load tables from an alternate schema
  --tables TABLES    tables to process (comma-separated, default: all)
  --noviews          ignore views
  --noindexes        ignore indexes
  --noconstraints    ignore constraints
  --nojoined         don't autodetect joined table inheritance
  --noinflect        don't try to convert tables names to singular form
  --outfile OUTFILE  file to write output to (default: stdout)

An example of the actual command would then be:

% sqlacodegen --outfile models.py \
postgresql://gollyjer:swordfish@localhost:5432/mydatabase

Where gollyjer:swordfish are your credentials in the format user:password.

Solution 2:

Few things which are already answered here:

1. sqlacodegen should be installed using pip
2. once installed it should be run from windows command prompt and not from python shell.
3. if you are giving multiple table names do not give any space between table names provide only commas.

Solution 3:

As @Antti suggest, sqlacodegen is supposed to be used from command shell.

Anyway, it is possible to embed the code generation in your own code using the CodeGenerator class and SqlAlchemy:

import io
import sys
from sqlalchemy import create_engine, MetaData
from sqlacodegen.codegen import CodeGenerator

def generate_model(host, user, password, database, outfile = None):
    engine = create_engine(f'postgresql+psycopg2://{user}:{password}@{host}/{database}')
    metadata = MetaData(bind=engine)
    metadata.reflect()
    outfile = io.open(outfile, 'w', encoding='utf-8') if outfile else sys.stdout
    generator = CodeGenerator(metadata)
    generator.render(outfile)

if __name__ == '__main__':
    generate_model('database.example.org', 'dbuser', 'secretpassword', 'mydatabase', 'db.py')

This will create the database model in db.py file.