agronholm / sqlacodegen Goto Github PK
View Code? Open in Web Editor NEWAutomatic model code generator for SQLAlchemy
License: Other
Automatic model code generator for SQLAlchemy
License: Other
describe as title
Originally reported by: Tobiiiiiiiiiii Tobiiiiiiiiiii (Bitbucket: TTTheKing, GitHub: TTTheKing)
when i defined my column in postgresql as serial i got this
ID = Column(Integer, primary_key=True, server_default=text("nextval('"Question_ID_seq"'::regclass)"))
in the text for server_default are errors because of the missing string escaping.
Originally reported by: hamx0r (Bitbucket: hamx0r, GitHub: hamx0r)
Often DBs are read from to create JSON responses as part of a REST API. It is trivial to convert SQL Alchemy Queries to a list of dictionaries for easy use with Python's built-in json library. However, datatypes do not get represented well, especially dialectal datatypes. For reasons below, it would create more flexible models (ie json
compatible, DB agnostic) if a model used more basic Column types and nondialectal datatypes
SQL Alchemy is able to do the following:
json
compatibility (which does not display Numeric values as numbers.Originally reported by: face toe (Bitbucket: facetoe, GitHub: facetoe)
A sequence with mixed case is generated without the necessary escape characters for the double quotes. For example, the sequence playbookRun_id_seq
is generated as:
id = Column(Integer, primary_key=True, server_default=text("nextval('"playbookRun_id_seq"'::regclass)"))
When it should be:
id = Column(Integer, primary_key=True, server_default=text("nextval('\"playbookRun_id_seq\"'::regclass)"))
BTW, thanks for your work on this tool, it's awesome.
Originally reported by: jonorthwash (Bitbucket: jonorthwash, GitHub: jonorthwash)
Using MySQL with InnoDB support, sqlite3, and probably numerous other engines allows for the specification of foreign keys directly in the database.
sqlacodegen responds to these specifications correctly in that it creates code for these relationships. However, in the code, it places certain classes after other classes that link to them.
For example, if a Participant table/class has a foreign key in an Individual table/class, the Individual class may follow the Participant class, resulting in sqlalchemy throwing an error like the following:
#!python
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'participants.id' could not find table 'individuals' with which to generate a foreign key to target column 'id'
The classes can be rearranged manually for expected results (i.e., no errors being thrown).
Type translation does not seem to work in array data types, e.g.:
create table dpa_test(dpa double precision[]);
produces
t_dpa_test = Table( 'dpa_test', metadata, Column('dpa', ARRAY(DOUBLE_PRECISION(precision=53))) )
Yet, DOUBLE_PRECISION is not imported.
I am unable to find any resource which shows how to use sqlacodegen with Teradata. I tried this syntax:
sqlacodegen teradata://<username>:<password>@host --outfile models.py
This gives the following error trace:
Traceback (most recent call last): File "c:\users\212628419\appdata\local\programs\python\python36\lib\runpy.py", line 193, in _run_module_as_main "main", mod_spec) File "c:\users\212628419\appdata\local\programs\python\python36\lib\runpy.py", line 85, in _run_code exec(code, run_globals) File "C:\Users\212628419\AppData\Local\Programs\Python\Python36\Scripts\sqlacodegen.exe__main__.py", line 9, in File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlacodegen\main.py", line 40, in main metadata.reflect(engine, args.schema, not args.noviews, tables) File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\sql\schema.py", line 3962, in reflect Table(name, self, **reflect_opts) File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\sql\schema.py", line 456, in new metadata._remove_table(name, schema) File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in exit compat.reraise(exc_type, exc_value, exc_tb) File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise raise value File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\sql\schema.py", line 451, in new table._init(name, metadata, *args, **kw) File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\sql\schema.py", line 533, in _init include_columns, _extend_on=_extend_on) File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\sql\schema.py", line 546, in _autoload _extend_on=_extend_on File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1545, in run_callable return callable_(self, *args, **kwargs) File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\default.py", line 389, in reflecttable table, include_columns, exclude_columns, **opts) File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\reflection.py", line 618, in reflecttable table_name, schema, **table.dialect_kwargs): File "c:\users\212628419\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\reflection.py", line 374, in get_columns col_def['type'] = coltype() TypeError: 'NoneType' object is not callable
I understand that we need to specify the driver for connecting to teradata, but the following syntax:
sqlacodegen: error: unrecognized arguments: Database\ ODBC\ Driver\ 16.10 --op.py
Is there a way to generate the models file from sqlalchemy-teradata? I tried automap and reflections both of which gives the same error trace as the first except for the first line in error trace being:
File "C:\Users\212628419\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\ext\automap.py", line 754, in prepare autoload_replace=False
Originally reported by: Rami Abughazaleh (Bitbucket: ramiabughazaleh, GitHub: ramiabughazaleh)
Hi.
Thank you for sqlacodegen 1.1.6.
A PEP 8 coding style violation occurs if the generated code has lines that are longer than 120 characters.
I'm thinking we can run autopep8 on the generated file to automatically fix things like this for us.
https://pypi.python.org/pypi/autopep8
Thank you.
class TableA(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
name = Column(String(128), index=True)
The index is generated without name.
How can i generate index with it's name.
when the column key is
something\n and\n something
,
there will be raising
"sqlalchemy\dialects\mysql\base.py:3136: SAWarning: Unknown column definition u' `something'"
Originally reported by: Charles Heizer (Bitbucket: ceh329, GitHub: ceh329)
When creating a models file using sqlacodegen none of my index's are being created and I get the following errors reported over and over. I really don't want to ignore the indexes.
/Users/dev/PycharmProjects/MacPatchWS/env/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2667: SAWarning: Unknown schema content: u" KEY `idx_puuid` (`puuid`) COMMENT '(null)'"
return parser.parse(sql, charset)
/Users/dev/PycharmProjects/MacPatchWS/env/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2667: SAWarning: Unknown schema content: u" UNIQUE KEY `idx_rid` (`rid`) COMMENT '(null)',"
return parser.parse(sql, charset)
Originally reported by: Joshua Kugler (Bitbucket: jjkugler, GitHub: Unknown)
We have a very large database in MS SQL. 337 tables. Over 13,500 lines of code produced with sqlacodegen. When trying to use the models, we got a couple minor errors:
The major error was this:
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'AdminContacts' and 'AdminContactsXRoles'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.
BTW, this is version 1.1.6, and the Version drop-down below doesn't have that option.
Originally reported by: CyberJacob (Bitbucket: CyberJacob, GitHub: CyberJacob)
When trying to use sqlacodegen to analyse some Microsoft SQL server tables, a sqlalchemy.exc.DBAPIError exception is thrown, with the message
(pyodbc.Error) ('07006', '[07006] [Microsoft][ODBC Driver 11 for SQL Server]Restricted data type attribute violation (0) (SQLGetData)')
I'm not entirely sure if this is caused by pyodbc or sqlacodegen, though I haven't managed to do anything in sqlalchemy that recreates the issue, so I'm inclined to think it's a sqlacodegen issue.
The specific command I ran was:
sqlacodegen mssql+pyodbc://lansync/ --schema dbo --tables SyncRoomTypes
The full traceback:
Traceback (most recent call last):
File "c:\python27\lib\runpy.py", line 174, in _run_module_as_main
"__main__", fname, loader, pkg_name)
File "c:\python27\lib\runpy.py", line 72, in _run_code
exec code in run_globals
File "C:\Python27\Scripts\sqlacodegen.exe\__main__.py", line 9, in <module>
File "c:\python27\lib\site-packages\sqlacodegen\main.py", line 40, in main
metadata.reflect(engine, args.schema, not args.noviews, tables)
File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 3804, in reflect
Table(name, self, **reflect_opts)
File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 436, in __new__
metadata._remove_table(name, schema)
File "c:\python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 431, in __new__
table._init(name, metadata, *args, **kw)
File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 507, in _init
self._autoload(metadata, autoload_with, include_columns)
File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 519, in _autoload
self, include_columns, exclude_columns
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1528, in run_callable
return callable_(self, *args, **kwargs)
File "c:\python27\lib\site-packages\sqlalchemy\engine\default.py", line 364, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 590, in reflecttable
table_name, schema, **table.dialect_kwargs):
File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 369, in get_columns
**kw)
File "<string>", line 2, in get_columns
File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 54, in cache
ret = fn(self, con, *args, **kw)
File "c:\python27\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1570, in wrap
tablename, dbname, owner, schema, **kw)
File "c:\python27\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1579, in _switch_db
return fn(*arg, **kw)
File "c:\python27\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1967, in get_columns
row = cursor.first()
File "c:\python27\lib\site-packages\sqlalchemy\engine\result.py", line 1203, in first
self.cursor, self.context)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1393, in _handle_dbapi_exception
exc_info
File "c:\python27\lib\site-packages\sqlalchemy\util\compat.py", line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "c:\python27\lib\site-packages\sqlalchemy\engine\result.py", line 1199, in first
row = self._fetchone_impl()
File "c:\python27\lib\site-packages\sqlalchemy\engine\result.py", line 1056, in _fetchone_impl
return self.cursor.fetchone()
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('07006', '[07006] [Microsoft][ODBC Driver 11 for SQL Server]Restricted data type attribute violation (0) (SQLGetData)')
And here's the output of exec sp_columns SyncRoomTypes
, which describes the table layout:
COLUMN_NAME TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
SyncRoomTypeId int identity 10 4 0 10 0 NULL NULL 1 NO 56
SyncTimestamp datetime 23 16 3 NULL 1 3 NULL 2 YES 111
GroupId nvarchar 12 24 NULL NULL 0 NULL 24 3 NO 39
SourceSiteId nvarchar 12 24 NULL NULL 0 NULL 24 4 NO 39
RoomTypeCode nvarchar 12 24 NULL NULL 0 NULL 24 5 NO 39
Description nvarchar 255 510 NULL NULL 0 NULL 510 6 NO 39
RoomTypeClass int 10 4 0 10 0 NULL NULL 7 NO 56
RFlag int 10 4 0 10 0 NULL NULL 8 NO 56
Hello Folks,
I'm trying to generate models from a Database that is remote, but I'm receiving the following error:
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
The command used to produce the error was:
sqlacodegen postgresql+psycopg2:///moodle:[email protected]/moodle
Thanks in Advance.
Originally reported by: YKdvd NA (Bitbucket: YKdvd, GitHub: YKdvd)
I was trying to run sqlacodegen on a MySQL database which does FK constraints to a different schema. So the Parent schema has a Users table, which includes a userGroup field which is an ENUM. The Child schema has a Something table which does something like:
CONSTRAINT fk_S_U
FOREIGN KEY (user_id
) REFERENCES Parent
.Users
(id
) ON UPDATE CASCADE
If I dump the Parent schema with sqlacodegen, there's no problem, and Users dumps fine. I can also dump tables from the Child schema that do FK constraints to other tables (not Users) in the Parent schema. But a Child table that refers to Parent.Users crashes with:
File ".../sqlacodegen/codegen.py", line 469, in init
if isinstance(table.c[colname].type, String):
File "../sqlalchemy/util/_collections.py", line 154, in getitem
return self._data[key]
KeyError: u'users.`userGroup'
I'm not sure why it doesn't like that Parent.Users.userGroup field cross-schema, perhaps something to do with it being an enum? This is sqlacodegen 1.1.1 and SQLAlchemy 0.8.2, against MySQL 5.5/5.1. I'll see if I can dig into the code, or create a minimal example reduction someday when I have free time.
Originally reported by: y g (Bitbucket: ubershmekel, GitHub: ubershmekel)
Calling this many times causes a few different classes and fields to be in the wrong order sometimes.
c:\python33\scripts\sqlacodegen.exe mysql+pymysql://user:pw@host/mydb > models.py
The resulting models.py file is ~700 lines long for your tests. I apologize I can't provide my actual data.
Originally reported by: Anonymous
The foreign keys in my database have "on delete cascade" on them, but this isn't showing up in the generated models, neither on the foreign keys nor on the relationships.
I am not sure if this codebase is still being actively maintained, but I noticed what I believe is unexpected behavior when generating code for Sqlalchemy core models (noclass=True, inflict=True). Is there a reason why these class instances are generated with a "t_" i.e.:
from sqlalchemy import Char, Column, MetaData, Table
from sqlacodegen.codegen import ModelTable
meta = MetaData()
table = Table('foobar', meta, Column('foo', CHAR(3), primary_key=True))
model_table = ModelTable(table)
>>> print(model_table).render())
>>> t_foobar = Table('foobar', metadata, Column('foo', String(3), primary_key=True))
This may seem patently obvious to SQLAlchemy veterans, but when sqlacodegen
found tables missing primary keys and produced Table
instances for these rather than proper classes, I was pretty flummoxed at first.
Fortunately I found a couple posts in the sqlalchemy Google Group that prompted me to have a closer look.
I get that tables with no primary keys of their own could just be relationship tables, or some other perfectly valid, unexceptional reason that's not worth making noise about when the script runs. But perhaps this should be mentioned in the README all the same?
Originally reported by: inklesspen (Bitbucket: inklesspen, GitHub: inklesspen)
I frequently use sqlacodegen to generate Table structures; this means I have to generate the declarative classes, then transform each class into the corresponding Table/Column setup. Since sqlacodegen can generate Tables, please add a command-line flag to always generate them.
Originally reported by: Rami Abughazaleh (Bitbucket: ramiabughazaleh, GitHub: ramiabughazaleh)
Thank you for sqlacodegen 1.1.6.
I'd like to request the ability to add generic docstrings to the models/tables.
For example:
#!python
class Student(Base):
"""
Student
"""
class ClassRoom(Base):
"""
Class Room
"""
In this way, when I inspect my code using PyCharm, it doesn't produce a warning indicating that a docstring is missing for example.
Thank you
Originally reported by: Jean-Paul Calderone (Bitbucket: exarkun, GitHub: exarkun)
A column with type TIMESTAMP ends up with a Column(DateTime()) in the generated model.
This is presumably because Model.__init__
tries to down-adapt TIMESTAMP to something generic based on the SQLAlchemy class hierarchy and TIMESTAMP subclasses DateTime in SQLAlchemy.
This definitely breaks the type of the column and does so very obviously when the column has a default like CURRENT_TIMESTAMP
which is not an allowed value for a MySQL DATETIME column.
Originally reported by: nickretallack (Bitbucket: nickretallack, GitHub: nickretallack)
Schema:
id uuid primary key DEFAULT uuid_generate_v4() NOT NULL
Generates:
id = Column(UUID, primary_key=True)
The server default isn't there.
Schema:
time timestamp not null default (now() at time zone 'utc'),
Generates:
time = Column(DateTime, nullable=False, server_default=u"timezone('utc'::text, now())")
Which generates:
time TIMESTAMP WITHOUT TIME ZONE DEFAULT 'timezone('utc'::text, now())' NOT NULL,
This has bad quoting.
Originally reported by: Peter Harding (Bitbucket: peterlharding, GitHub: peterlharding)
I have experienced the following error with several of the databases I have attempted to use the utility with:
Traceback (most recent call last):
File "/usr/local/bin/sqlacodegen", line 9, in
load_entry_point('sqlacodegen==1.1.4', 'console_scripts', 'sqlacodegen')()
File "/usr/local/lib/python2.7/dist-packages/sqlacodegen/main.py", line 40, in main
generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined, args.noinflect)
File "/usr/local/lib/python2.7/dist-packages/sqlacodegen/codegen.py", line 520, in init
model = ModelClass(table, links[table.name], inflect_engine, not nojoined)
File "/usr/local/lib/python2.7/dist-packages/sqlacodegen/codegen.py", line 263, in init
super(ModelClass, self).init(table)
File "/usr/local/lib/python2.7/dist-packages/sqlacodegen/codegen.py", line 205, in init
column.type = column.type.adapt(cls)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/dialects/mysql/base.py", line 1175, in adapt
return sqltypes.Enum.adapt(self, impltype, **kw)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py", line 1145, in adapt
**kw
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py", line 1110, in init
SchemaType.init(self, **kw)
TypeError: init() got an unexpected keyword argument 'strict'
I am running on a Debian 7 system using Python 2.7.5 and have the following packages installed:
albatross (1.42)
argparse (1.2.1)
Babel (1.3)
Beaker (1.6.3)
blinker (1.3)
bzr (2.6.0dev3)
bzr-etckeeper (0.0.0)
chardet (2.0.1)
configobj (4.7.2)
decorator (3.4.0)
distribute (0.6.49)
ez-setup (0.9)
Flask (0.10.1)
Flask-Babel (0.9)
Flask-Login (0.2.6)
Flask-Mail (0.9.0)
Flask-OpenID (1.1.1)
Flask-SQLAlchemy (0.16)
Flask-WhooshAlchemy (0.54a)
Flask-WTF (0.9.4)
flup (1.0.2)
fpconst (0.7.2)
fpdf (1.7)
httplib2 (0.7.4)
inflect (0.2.4)
ipython (0.13.2)
itsdangerous (0.23)
Jinja2 (2.7.2)
keyring (1.6)
launchpadlib (1.9.12)
lazr.restfulclient (0.13.3)
lazr.uri (1.0.3)
MarkupSafe (0.18)
mercurial (2.6.3)
migrate (0.2.2)
moin (1.9.7)
MySQL-python (1.2.3)
numpy (1.7.1)
oauth (1.0.1)
paramiko (1.10.1)
parsedatetime (0.8.7)
passlib (1.6.1)
pbr (0.5.23)
pexpect (2.4)
PIL (1.1.7)
pip (1.5)
ply (3.4)
pycrypto (2.6)
pygame (1.9.1release)
Pygments (1.6)
pygobject (3.8.2)
pygpgme (0.2)
PyICU (1.5)
pyinotify (0.9.3)
pyOpenSSL (0.13)
PySimpleSOAP (1.10)
pysqlite (2.6.3)
python-apt (0.8.9)
python-dateutil (1.5)
python-debian (0.1.21-nmu2)
python-debianbts (1.11)
python-distutils-extra (2.38)
python-openid (2.2.5)
pytz (2013.8)
recaptcha-client (1.0.6)
reportbug (6.4.4)
reportlab (2.5)
scipy (0.12.0)
SecretStorage (1.0.0)
Originally reported by: Cristian Mitroi (Bitbucket: cristianxyz, GitHub: Unknown)
If a table has more than 255 columns, the "Table" class will be passed all those as such. This is a syntax error in python.
I have such a database. I was trying to test out sqlacodegen on it, to see if it could handle. It seems this is a breaking point.
Any known workarounds? Is it a know limitation? Isn't there any other way of creating tables? Maybe by using a list of the arguments, instead of the usual tuple?
Generating a many-to-many relationship, the name used in the secondary parameter was the table, instead of the variable.
Example:
t_authoring = Table(
'authoring', metadata,
Column('author', ForeignKey('public.authors.author'), primary_key=True, nullable=False),
Column('mafid', ForeignKey('public.items.mafid'), primary_key=True, nullable=False),
schema='public'
)
class Author(Base):
__tablename__ = 'authors'
__table_args__ = {'schema': 'public'}
author = Column(String(80), primary_key=True)
items = relationship('Item', secondary='authoring')
If I replace secondary='authoring'
with secondary=t_authoring
the problem seems solved.
Originally reported by: li_aung_yip (Bitbucket: li_aung_yip, GitHub: Unknown)
When attempting to import an SQLite database where DEFAULT
values contain Unicode, we receive a UnicodeEncodeError
:
Traceback (most recent call last):
File "C:\Python27\lib\runpy.py", line 162, in _run_module_as_main
"__main__", fname, loader, pkg_name)
File "C:\Python27\lib\runpy.py", line 72, in _run_code
exec code in run_globals
File "C:\Python27\Scripts\sqlacodegen.exe\__main__.py", line 9, in <module>
File "C:\Python27\lib\site-packages\sqlacodegen\main.py", line 41, in main
generator.render(args.outfile)
File "C:\Python27\lib\site-packages\sqlacodegen\codegen.py", line 567, in render
print('\n\n' + model.render().rstrip('\n'), file=outfile)
File "C:\Python27\lib\encodings\cp850.py", line 12, in encode
return codecs.charmap_encode(input,errors,encoding_map)
UnicodeEncodeError: 'charmap' codec can't encode character u'\u2013' in position 849: character maps to <undefined>
The offending table schema (in part):
"table" "dnd_monster" "CREATE TABLE "dnd_monster" (
"grapple" smallint(6) NOT NULL,
"attack" varchar(128) NOT NULL DEFAULT '+3 greatsword +23 melee (3d6+13/19–20) or slam +20 melee (2d8+10)',
"full_attack" varchar(128) NOT NULL DEFAULT '+3 greatsword +23/+18/+13 melee (3d6+13/19–20) or slam +20 melee (2d8+10)',
PRIMARY KEY ("id")
)" "dnd_monster"
Note that the dash in 19–20
unicode u+2013 EN DASH
.
Platform is Windows 7.
C:\Users\lws\>sqlacodegen --version
1.1.5
C:\Users\lws\>python --version
Python 2.7.9
PS C:\Users\lws\> systeminfo
[snip...]
System Locale: en-au;English (Australia)
Input Locale: en-us;English (United States)
Originally reported by: Victor Prosolin (Bitbucket: exfizik, GitHub: exfizik)
When I run sqlacodegen on one of our databases, I get some fields that look like this (not single quotes around a multiline string):
#!python
WEB_IND = Column(String(1, u'SQL_Latin1_General_CP1_CI_AS'), nullable=False, server_default=text(" /*==============================================================*/
/* DBMS name: Microsoft SQL Server 2000 XXXX */
/* Created on: 09/06/2008 9:17:21 PM */
/*==============================================================*/
/*==============================================================*/
/* Default: _NO_ */
/*==============================================================*/
create default _NO_
as 'N'
"))
My setup is Sqlalchemy 0.9.1, Pymssql 2.0.1, Sqlacodegen 1.1.5pre (tested with 1.1.4 as well). MS SQL Server 2012 (DB migrated from SQL Server 2005), Python 2.7.5. Opensuse Linux 12.3 and Centos 6 64 bit.
P.S. I have no idea how this stuff ended up in my DB, it's a legacy thing.
Originally reported by: Qiaoliang Xiang (Bitbucket: qiaoliangxiang, GitHub: qiaoliangxiang)
Problem:
I used this tool to build ORM models from redshift, and I found one generated column
Column('xxx', Integer, nullable=False, server_default=text(""identity"(187947, 0, '1,1'::text)")).
""identity"(187947, 0, '1,1'::text)" is an invalid Python expression.
the default_expr
is "identity"(187947, 0, '1,1'::text), which contains double quotes.
The server default simply put a double quote around it.
Solution
need to escape double quotes
How to fix:
file: codegen.py
line: 487
change:
server_default = 'server_default=text("{0}")'.format(default_expr)
to :
server_default = 'server_default=text("{0}")'.format(default_expr.replace('"', '\"'))
Originally reported by: cao guilin (Bitbucket: shcaoguilin, GitHub: shcaoguilin)
--postgre9.4
--table def:
CREATE TABLE scheama01."table01"
(
"ID" bigint NOT NULL,
"ParentID" bigint,
CONSTRAINT "table01_pkey" PRIMARY KEY ("ID"),
CONSTRAINT "table01_ID_fkey" FOREIGN KEY ("ID")
REFERENCES scheama01."Location" ("ID") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "table01_ParentID_fkey" FOREIGN KEY ("ParentID")
REFERENCES scheama01."table01" ("ID") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
#error i got:
caogl@zhost:~/sqlacodegen$ sqlacodegen postgresql://postgres:@hg.z.com:5437/db01 --schema scheama01 --tables "table01"
Traceback (most recent call last):
File "/usr/local/bin/sqlacodegen", line 9, in
load_entry_point('sqlacodegen==1.1.5', 'console_scripts', 'sqlacodegen')()
File "/usr/local/lib/python2.7/dist-packages/sqlacodegen-1.1.5-py2.7.egg/sqlacodegen/main.py", line 40, in main
generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined, args.noinflect)
File "/usr/local/lib/python2.7/dist-packages/sqlacodegen-1.1.5-py2.7.egg/sqlacodegen/codegen.py", line 545, in init
classes[model.parent_name].children.append(model)
KeyError: u'table01'
Originally reported by: Ishan Khare (Bitbucket: ishan_khare, GitHub: Unknown)
just like the --tables
option which specifies the tables to be processed, there should be a --skip-tables
option. This will be particularly helpful if i have only a few tables to skip among lots of tables.
Originally reported by: mltmlt (Bitbucket: mltmlt, GitHub: Unknown)
Some system tables lack primary key but use OID instead.
The following fails to generate a proper model
sqlacodegen postgresql://[email protected]/postgres --tables pg_database --schema pg_catalog --outfile pg_database.py
with KeyError: u'oid'
.
Originally reported by: hamx0r (Bitbucket: hamx0r, GitHub: hamx0r)
When using sqlacodegen
with the --outfile
flag and without the --schema
flag, it creates an incomplete model (ie it ignores some tables, so classes are missing)
If i add the --schema
flag, it throws this warning:
#!bash
/usr/local/lib64/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2421: SAWarning: Did not recognize type 'unknown' of column 'location_type'
(attype, name))
...after which it creates a "complete" models file which contains erroneous double quotes. This issue relates to #17 .
I'm using sqlacodegen 1.1.6 on Postgres.
For example, a schema called sms
with a table called flights
uses a PK called id
of type bigint
, and the resulting model info looks like this:
#!python
class Flight(Base):
__tablename__ = 'flights'
...
id = Column(BigInteger, primary_key=True, server_default=text("nextval('"sms".flights_new_id_seq'::regclass)"))
...
Note the doublequotes around "sms"
and now they interfere with the doublequotes starting before "nextval
A similar thing happens whenever a function is used in a default value of a column in the schema. The server_default=text()
argument should perhaps be triplequoted so that all internal double and single quotes remain part of the text string, or else the quotes need to be escaped.
some enum content is generated to be question mark
create table test
(testing enum('你好', 'hello') default '你好'
);
+----------+----------------------------+---------+------+-----------+--------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+---------+------+-----------+--------+
| testing | enum('你好','hello') | YES | | 你好 | |
+----------+----------------------------+---------+------+------------+-------+
t_test = Table(
'test', metadata,
Column('testing', ENUM('??', 'hello'), server_default=text("'??'"))
)
Invalid Identifier error after generating models for table with unicode chars in table name when importing the models.
class 💞aussieBabe💞Index(Base):
^
SyntaxError: invalid character in identifier
Originally reported by: jmagnusson (Bitbucket: jmagnusson, GitHub: jmagnusson)
The following error is produced when I run $ sqlacodegen mysql+oursql://myuser:mypassword@localhost/mydb
with SQLAlchemy 0.9. I don't get this with 0.8.4.
/Users/jacob/.venvs/vsync/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2518: SAWarning: Did not recognize type 'polygon' of column 'marking'
self._parse_column(line, state)
Traceback (most recent call last):
File "/Users/jacob/.venvs/vsync/bin/sqlacodegen", line 9, in <module>
load_entry_point('sqlacodegen==1.1.4', 'console_scripts', 'sqlacodegen')()
File "/Users/jacob/.venvs/vsync/lib/python2.7/site-packages/sqlacodegen/main.py", line 40, in main
generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined, args.noinflect)
File "/Users/jacob/.venvs/vsync/lib/python2.7/site-packages/sqlacodegen/codegen.py", line 520, in __init__
model = ModelClass(table, links[table.name], inflect_engine, not nojoined)
File "/Users/jacob/.venvs/vsync/lib/python2.7/site-packages/sqlacodegen/codegen.py", line 263, in __init__
super(ModelClass, self).__init__(table)
File "/Users/jacob/.venvs/vsync/lib/python2.7/site-packages/sqlacodegen/codegen.py", line 205, in __init__
column.type = column.type.adapt(cls)
File "/Users/jacob/.venvs/vsync/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1175, in adapt
return sqltypes.Enum.adapt(self, impltype, **kw)
File "/Users/jacob/.venvs/vsync/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py", line 1145, in adapt
**kw
File "/Users/jacob/.venvs/vsync/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py", line 1110, in __init__
SchemaType.__init__(self, **kw)
TypeError: __init__() got an unexpected keyword argument 'strict'
Originally reported by: xicesky (Bitbucket: xicesky, GitHub: xicesky)
Lets say i'd like to modify sqlacodegen in a few subtle ways (e.g. generate code for columns another way).
At the moment i would have to rewrite most of the code, because just subclassing ModelTabel or ModelClass will not make the main CodeGen actually use it. Worse yet, the private utility functions can not (in an obvious way) be "subclassed".
Plus, the Codegen implementation does all its work in one method - the constructor.
Originally reported by: Victor Prosolin (Bitbucket: exfizik, GitHub: exfizik)
I have a bunch of tables in the DB with a naming scheme like TBL_CONF_SOMETHING which are mapped to classes as class TBLCONFSOMETHING.
And then I have some "correlation tables" which only have foreign key fields. those are represented as Tables, e.g.
t_TBL_CONF_SOMETHINGELSECORR = Table(...)
These correlation tables are references in some relationship fields. E.g.
#!python
class TBLCONFDATACOLUMNS(Base):
__tablename__ = 'TBL_CONF_DATACOLUMNS'
__table_args__ = {u'schema': 'dbo'}
COLUMN_ID = Column(Numeric(6, 0), primary_key=True)
COLUMN_NAME = Column(String(50, u'SQL_Latin1_General_CP1_CI_AS'), nullable=False)
DESCRIPTION = Column(String(50, u'SQL_Latin1_General_CP1_CI_AS'), nullable=False)
TBL_CONF_BUILDINGLEVELS = relationship(u'TBLCONFBUILDINGLEVELS', secondary='TBL_WEB_COLUMNBUILDINGLEVELCORR')
TBL_SRC_SPECIESTYPE = relationship(u'TBLSRCSPECIESTYPE', secondary='TBL_CONF_SPECIESCOLUMNCORR')
t_TBL_WEB_COLUMNBUILDINGLEVELCORR = Table(
'TBL_WEB_COLUMNBUILDINGLEVELCORR', metadata,
Column('COLUMN_ID', ForeignKey(u'dbo.TBL_CONF_DATACOLUMNS.COLUMN_ID'), primary_key=True, nullable=False),
Column('PROJECT_ID', Numeric(6, 0), primary_key=True, nullable=False),
Column('LEVEL', Integer, primary_key=True, nullable=False),
ForeignKeyConstraint(['PROJECT_ID', 'LEVEL'], [u'dbo.TBL_CONF_BUILDINGLEVELS.PROJECT_ID', u'dbo.TBL_CONF_BUILDINGLEVELS.LEVEL']),
schema='dbo'
)
But then when I'm trying to query something, I get the following error:
InvalidRequestError: When initializing mapper Mapper|TBLCONFDATACOLUMNS|TBL_CONF_DATACOLUMNS, expression 'TBL_WEB_COLUMNBUILDINGLEVELCORR' failed to locate a name ("name 'TBL_WEB_COLUMNBUILDINGLEVELCORR' is not defined"). If this is a class name, consider adding this relationship() to the <class 'ess_db_util.db_models.envision.dbo.TBLCONFDATACOLUMNS'> class after both dependent classes have been defined.
Looks like the only way to fix it is to remove those relationships pointing to non-existing classes.
This looks like a bug. What do you think? Or maybe there is a way to map these correlation tables as classes, like all the other tables?
I'm using version 1.1.6, MS SQL Server 2012 and pymssql backend.
Please let me know if you need more information.
Thank you.
As discussed previously on stackoverflow, some users would like to generate schemas with all tables represented as SQLAlchemy classes.
Would it be possible to add an --onlyclasses
argument to sqlacodegen?
Many thanks
Originally reported by: Oliver Berger (Bitbucket: diefans, GitHub: diefans)
diff -r 5c5d2a69d806 sqlacodegen/codegen.py
#!diff
--- a/sqlacodegen/codegen.py Sun Sep 01 17:25:34 2013 +0300
+++ b/sqlacodegen/codegen.py Tue Oct 08 12:04:31 2013 +0200
@@ -20,7 +20,7 @@
_re_boolean_check_constraint = re.compile(r"(?:(?:.*?)\.)?(.*?) IN \(0, 1\)")
-_re_column_name = re.compile(r'(?:(["`])(?:.*)\1\.)?(["`]?)(.*)\2')
+_re_column_name = re.compile(r'(?:(["`]?)(?:.*)\1\.)?(["`]?)(.*)\2')
_re_enum_check_constraint = re.compile(r"(?:(?:.*?)\.)?(.*?) IN \((.+)\)")
_re_enum_item = re.compile(r"'(.*?)(?<!\\)'")
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.