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?
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.
Few things which are already answered here:
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.
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)
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"]
)
...
