How to run sqlacodegen?

Question

I'm trying to run sqlacodegen to create a SQLAlchemy model from an existing PostgreSQL database.

It won't run. When I type sqlacodegen --help I get:

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

Basic instructions are here.

How do you get sqlacodegen to run?

Answer 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.

Answer 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.

Answer 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.

Answer 4

This is how I got it to work. Create a virtual environment in Python and activate it in the shell (bash, terminal). Create an env_file.env that should be filled like:

DB_NAME=x
DB_USER=y
DB_PASS=z
DB_HOST=MY_HOST.com
DB_PORT=3306 (default, or take your port)
SCHEMA_NAME=a
TABLE_NAMES=b,c,d
FULL_PATH=./test

and run

source env_file.env
# check:
echo $DB_PORT
# Should give you 3306

Then run:

pip install pymysql
pip install sqlacodegen

sqlacodegen mysql+pymysql://$DB_USER:$DB_PASS@$DB_HOST:$DB_PORT/$DB_NAME?charset=utf8 --schema $SCHEMA_NAME --tables $TABLE_NAMES --outfile $FULL_PATH

And you will see a new file "test" in your folder with all of the information you can get from the tables "b", "c" and "d".

The output pattern looks like:

# coding: utf-8
from sqlalchemy import CHAR, Column, DateTime, Float, Index, String, TIMESTAMP, text
from sqlalchemy.dialects.mysql import CHAR, INTEGER, LONGTEXT, MEDIUMINT, MEDIUMTEXT, TINYINT, VARCHAR
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Trip(Base):
    __tablename__ = 'trips'
    __table_args__ = (
        Index('id', 'p_id', 'trip_name', 'update_time'),
        {'schema': 'holiday', 'comment': 'InnoDB free: 1234 kB; (`t_id`) REFER `holiday/prices'}
    )

    id = Column(INTEGER(11), primary_key=True)
    p_id = Column(INTEGER(11), nullable=False)
    trip_name = Column(String(150, 'utf8_unicode_ci'), nullable=False, server_default=text("''"))
    update_time = Column(DateTime, index=True)



class Prices(Base):
    __tablename__ = 'prices'
    __table_args__ = (
        Index('t_id'),
        Index('update_time'),
        {'schema': 'holiday', 'comment': 'InnoDB free: 1234 kB; (`t_id`) REFER `holiday/trips'}
    )

    id = Column(INTEGER(11), nullable=False, index=True, server_default=text("'0'"))
    t_id = Column(INTEGER(11), nullable=False, index=True, server_default=text("'0'"))
    company_name = Column(String(150, 'utf8_unicode_ci'), nullable=False, server_default=text("''"))
    update_time = Column(TIMESTAMP, index=True)

Answer 5

I work with realtively large databases and have lived quite happily with running Daniel Garcias skript in my Dockerfiles and subsequently importing the created .py-file into my production code like self._tables = import_module('db') for quite a while.

However, sqlacodegen seems to be poorly maintained; it wouldn't work with sqlalchemy 2.0 and stopped working completely for me since ArgSpec has been deprecated. So I've been looking for a method to make do without it that I'd like to share in case someone encounters similar problems.

Actually, the MetaData object contains all the table information you need already, so what I do is to save that one using joblib:

import io
import sys
import joblib
from sqlalchemy import create_engine, MetaData

def generate_model(host, user, password, database, outfile = None):
    engine = create_engine(f'postgresql+psycopg2://{user}:{password}@{host}/{database}')
    metadata = MetaData()
    metadata.reflect(engine)
    joblib.dump(metadata, outfile)

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

In you production code, you can then do the following:

import joblib
import sqlalchemy

metadata = joblib.load(db.joblib)

stmt = sqlalchemy.select(
   metadata.tables["schema.table_name"].c["some_column"]
   )
...