# odbc2orm
This package convert an ODBC schema into a sqlalchemy ORM schema.
Its primary written for MS Access schemas, but it should work for all ODBC
relational databases. for this only the driver should be configured. And for
the target project extra packages may be needed (see **Required packages for target project**).
# Licence
This package is under GNU GENERAL PUBLIC LICENSE, Version 2 only.
Therefore article 9 of "GNU GENERAL PUBLIC LICENSE, Version 2, June 1991" does not apply.
See LICENCE file for the GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
# Required packages for target project
Assumming **sqlalchemy** is already in your project. The following two
packages are required when accessing MS Access databases;
* sqlalchemy-access
* pyodbc
For other ODBC database connections other packages may be required.
Currently this package is tested with MS Access databases.
# Usage
```shell
> python -m odbc2orm database.mdb
```
## Options
The following options are available at command line.
-v Verbose output.
-V/--version version output
-h/--help This help information.
-o/--output <filename> write Python code file, instead of stdout.
default STDOUT
-c/--config <filename> Configure driver, output and template files.
-d/--driver <driver> The ODBC driver to be used,
default "Microsoft Access Driver (*.mdb, *.accdb)"
-D/--dump Include the dump_tables() function in the output,
default: False
-t/--template <folder> Create template files and configuration YAML file
for personal customizing.
When using a configuration file **--output** and **--driver** may override the configuration
by setting those after the configuration option.
# Configuration
A default configuration file can be created through the **--template** option.
Typical configuration file looks like this;
```YAML
driver: Microsoft Access Driver (*.mdb, *.accdb)
template:
leadin: custom\template\leadin,templ
leadout: custom\template\leadout.templ
table_column: custom\template\table_column.templ
table_leadin: custom\template\table_leadin.templ
table_leadout: custom\template\table_leadout.templ
verbose: false
include_dump: false,
```
The **template** section are the Mako template files to generate the ORM schema
Python file.
# Templates
By using the **--template** command line option the standard templates and configuration
is exported to a specified folder. This gives the opportunity to customize the generation
of the output of the application.
## leadin and leadout
The leadin and leadout templates receive the following variables;
* **config**; dictionary with configuration as being used during generation.
* **datetime**; Python datetime module.
* **version**; string with the version of odbf2orm module.
* **copyright**; string with the copyright of odbf2orm module.
* **author**; string with the author of odbf2orm module.
* **username**; string with the current username from the Operating System.
## table_leadin and table_leadout
The table_leadin and table_leadout templates receive the following variables;
* **config**; dictionary with configuration as being used during generation.
* **table**; OdbcTable object
## table_column
The table_column template receive the following variables;
* **config**; dictionary with configuration as being used during generation.
* **table**; OdbcTable object
* **column**; OdbcColumn object
# ODBC objects
## OdbcTable
The OdbcTable object is actual a list object with extra properties, the list contains the OdbcColumn objects.
## Extra properies
* **name**; string with the table name.
* **catalog**; string with catalog name where the table belong to.
* **schema**; string with schema name where the table belong to.
## OdbcColumn
The OdbcColumn object contains the column properies;
* **table_cat**; string with catalog name where the column belong to.
* **schema**; string with schema name where the column belong to.
* **table_name**; string with the table name.
* **column_name**; string with column name.
* **data_type**; integer with the column data type, see for more information https://github.com/mkleehammer/pyodbc/wiki/Data-Types
* **type_name**; string with the column type name, see for more information https://github.com/mkleehammer/pyodbc/wiki/Data-Types
* **column_size**; the number of octets that are used to store the column data
* **buffer_length**; the number of octets that are used for the internal buffer in pyodbc
* **decimal_digit**; the number of decimal fraction digitis.
* **num_spec_radix**; ?
* **nullable**; integer (1/0) (**True**/**False**) if the column is nullable.
* **remarks**; string a description of the column.
* **column_default**: the column dafault value
* **sql_data_type**; ?
* **sql_datetime_sub**; ?
* **char_octet_length**; integer
* **ordinal_position**; integer with the column position in the row.
* **is_nullable**; string (**"YES"**/**"NO"**) if the column is nullable.
* **ordina**l; integer same as **ordinal_position**.
For some databases there are more properies available, those are not supported.
# Typical output
The following is a simple example of the output the odbc2orm tool shall generate with the
default builtin templates.
#
# This file was created by odbc2orm.py 1.0.5 Copyright (C) 2023 Marc Bertens-Nguyen
# odbc2orm.py comes with ABSOLUTELY NO WARRANTY. This is free software,
# and you are welcome to redistribute under GNU General Public License, version 2 only
#
# Source file created: username at 2023-04-16 15:06:57
# from test-database.mdb with driver Microsoft Access Driver (*.mdb, *.accdb)
#
from typing import Optional
from sqlalchemy import engine, create_engine
from sqlalchemy import Column, String, Integer, Text, Boolean, DECIMAL, FLOAT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class SomeTable( Base ):
__tablename__ = 'SomeTable'
Id = Column( Integer, primary_key = True )
Name = Column( String( 50 ), nullable = True )
Description = Column( String( 255 ), nullable = True )
Filename = Column( String( 200 ), nullable = True )
DataFormat = Column( Integer, nullable = True )
def __repr__(self):
return f"<SomeTable {self.Id}, Name = '{self.Name}', Description = '{self.Description}', Filename = '{self.Filename}'>"
__engine = None
__session = None
__connection = None
def getEngine( database: Optional[str] = None ):
driver = 'Microsoft Access Driver (*.mdb, *.accdb)'
if not database:
# This the file with this file was created.
database = 'G:\emagic\equens.mdb'
connection_string = f"DRIVER={{{driver}}};DBQ={database};;ExtendedAnsiSQL=1;"
connection_url = engine.URL.create( "access+pyodbc",
query = { "odbc_connect": connection_string } )
global __engine, __connection
__engine = create_engine( connection_url )
__connection = __engine.connect()
return __connection
def getConnection():
global __connection
return __connection
def getSession():
global __engine, __session
if not __engine:
raise Exception( "Connection not opened yet." )
if __session:
return __session
SessionObject = sessionmaker( bind = __engine )
__session = SessionObject()
return __session
def dump_tables():
getEngine()
session = getSession()
# Dump contents of table 'FileTp1'
for rec in session.query( FileTp1 ).all():
print( rec )
return
if __name__ == '__main__':
dump_tables()
Raw data
{
"_id": null,
"home_page": "https://github.com/pe2mbs/odbc2orm",
"name": "odbc2orm",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.6",
"maintainer_email": "",
"keywords": "odbc sqlalchemu orm",
"author": "Marc Bertens-Nguyen",
"author_email": "m.bertens@pe2mbs.nl",
"download_url": "https://files.pythonhosted.org/packages/9a/58/f82144e906af78d198c9d34f5bc0a6cfdebf4f0544885e3fab54c8512c62/odbc2orm-1.0.7.tar.gz",
"platform": null,
"description": "# odbc2orm\r\nThis package convert an ODBC schema into a sqlalchemy ORM schema.\r\nIts primary written for MS Access schemas, but it should work for all ODBC \r\nrelational databases. for this only the driver should be configured. And for \r\nthe target project extra packages may be needed (see **Required packages for target project**).\r\n\r\n\r\n# Licence\r\nThis package is under GNU GENERAL PUBLIC LICENSE, Version 2 only. \r\nTherefore article 9 of \"GNU GENERAL PUBLIC LICENSE, Version 2, June 1991\" does not apply. \r\n\r\nSee LICENCE file for the GNU GENERAL PUBLIC LICENSE, Version 2, June 1991\r\n\r\n\r\n# Required packages for target project\r\nAssumming **sqlalchemy** is already in your project. The following two \r\npackages are required when accessing MS Access databases; \r\n* sqlalchemy-access\r\n* pyodbc\r\n\r\nFor other ODBC database connections other packages may be required. \r\nCurrently this package is tested with MS Access databases.\r\n\r\n\r\n# Usage\r\n```shell\r\n\r\n> python -m odbc2orm database.mdb\r\n\r\n```\r\n\r\n\r\n## Options\r\nThe following options are available at command line. \r\n\r\n -v Verbose output.\r\n\r\n -V/--version version output \r\n\r\n -h/--help This help information.\r\n \r\n -o/--output <filename> write Python code file, instead of stdout.\r\n default STDOUT \r\n \r\n -c/--config <filename> Configure driver, output and template files.\r\n\r\n -d/--driver <driver> The ODBC driver to be used,\r\n default \"Microsoft Access Driver (*.mdb, *.accdb)\"\r\n\r\n -D/--dump Include the dump_tables() function in the output,\r\n default: False\r\n\r\n -t/--template <folder> Create template files and configuration YAML file\r\n for personal customizing. \r\n\r\nWhen using a configuration file **--output** and **--driver** may override the configuration \r\nby setting those after the configuration option.\r\n\r\n\r\n# Configuration \r\nA default configuration file can be created through the **--template** option.\r\n\r\nTypical configuration file looks like this;\r\n```YAML\r\ndriver: Microsoft Access Driver (*.mdb, *.accdb)\r\ntemplate:\r\n leadin: custom\\template\\leadin,templ\r\n leadout: custom\\template\\leadout.templ\r\n table_column: custom\\template\\table_column.templ\r\n table_leadin: custom\\template\\table_leadin.templ\r\n table_leadout: custom\\template\\table_leadout.templ\r\nverbose: false\r\ninclude_dump: false,\r\n```\r\nThe **template** section are the Mako template files to generate the ORM schema \r\nPython file.\r\n\r\n\r\n# Templates\r\nBy using the **--template** command line option the standard templates and configuration\r\nis exported to a specified folder. This gives the opportunity to customize the generation\r\nof the output of the application.\r\n\r\n\r\n## leadin and leadout\r\nThe leadin and leadout templates receive the following variables;\r\n* **config**; dictionary with configuration as being used during generation.\r\n* **datetime**; Python datetime module.\r\n* **version**; string with the version of odbf2orm module.\r\n* **copyright**; string with the copyright of odbf2orm module.\r\n* **author**; string with the author of odbf2orm module.\r\n* **username**; string with the current username from the Operating System.\r\n\r\n\r\n## table_leadin and table_leadout \r\nThe table_leadin and table_leadout templates receive the following variables;\r\n* **config**; dictionary with configuration as being used during generation.\r\n* **table**; OdbcTable object\r\n\r\n\r\n## table_column\r\nThe table_column template receive the following variables;\r\n* **config**; dictionary with configuration as being used during generation.\r\n* **table**; OdbcTable object\r\n* **column**; OdbcColumn object\r\n\r\n\r\n# ODBC objects\r\n## OdbcTable\r\nThe OdbcTable object is actual a list object with extra properties, the list contains the OdbcColumn objects.\r\n\r\n\r\n## Extra properies\r\n* **name**; string with the table name.\r\n* **catalog**; string with catalog name where the table belong to.\r\n* **schema**; string with schema name where the table belong to.\r\n\r\n\r\n## OdbcColumn\r\nThe OdbcColumn object contains the column properies; \r\n\r\n* **table_cat**; string with catalog name where the column belong to.\r\n* **schema**; string with schema name where the column belong to.\r\n* **table_name**; string with the table name.\r\n* **column_name**; string with column name. \r\n* **data_type**; integer with the column data type, see for more information https://github.com/mkleehammer/pyodbc/wiki/Data-Types\r\n* **type_name**; string with the column type name, see for more information https://github.com/mkleehammer/pyodbc/wiki/Data-Types\r\n* **column_size**; the number of octets that are used to store the column data\r\n* **buffer_length**; the number of octets that are used for the internal buffer in pyodbc\r\n* **decimal_digit**; the number of decimal fraction digitis. \r\n* **num_spec_radix**; ?\r\n* **nullable**; integer (1/0) (**True**/**False**) if the column is nullable. \r\n* **remarks**; string a description of the column.\r\n* **column_default**: the column dafault value\r\n* **sql_data_type**; ? \r\n* **sql_datetime_sub**; ?\r\n* **char_octet_length**; integer \r\n* **ordinal_position**; integer with the column position in the row.\r\n* **is_nullable**; string (**\"YES\"**/**\"NO\"**) if the column is nullable. \r\n* **ordina**l; integer same as **ordinal_position**.\r\n\r\nFor some databases there are more properies available, those are not supported.\r\n\r\n# Typical output\r\nThe following is a simple example of the output the odbc2orm tool shall generate with the \r\ndefault builtin templates.\r\n\r\n #\r\n # This file was created by odbc2orm.py 1.0.5 Copyright (C) 2023 Marc Bertens-Nguyen\r\n # odbc2orm.py comes with ABSOLUTELY NO WARRANTY. This is free software,\r\n # and you are welcome to redistribute under GNU General Public License, version 2 only\r\n #\r\n # Source file created: username at 2023-04-16 15:06:57\r\n # from test-database.mdb with driver Microsoft Access Driver (*.mdb, *.accdb)\r\n #\r\n from typing import Optional\r\n from sqlalchemy import engine, create_engine\r\n from sqlalchemy import Column, String, Integer, Text, Boolean, DECIMAL, FLOAT\r\n from sqlalchemy.ext.declarative import declarative_base\r\n from sqlalchemy.orm import sessionmaker\r\n \r\n \r\n Base = declarative_base()\r\n \r\n \r\n class SomeTable( Base ):\r\n __tablename__ = 'SomeTable'\r\n Id = Column( Integer, primary_key = True )\r\n Name = Column( String( 50 ), nullable = True )\r\n Description = Column( String( 255 ), nullable = True )\r\n Filename = Column( String( 200 ), nullable = True )\r\n DataFormat = Column( Integer, nullable = True )\r\n \r\n def __repr__(self):\r\n return f\"<SomeTable {self.Id}, Name = '{self.Name}', Description = '{self.Description}', Filename = '{self.Filename}'>\"\r\n\r\n\r\n __engine = None\r\n __session = None\r\n __connection = None\r\n \r\n \r\n def getEngine( database: Optional[str] = None ):\r\n driver = 'Microsoft Access Driver (*.mdb, *.accdb)'\r\n if not database:\r\n # This the file with this file was created.\r\n database = 'G:\\emagic\\equens.mdb'\r\n \r\n connection_string = f\"DRIVER={{{driver}}};DBQ={database};;ExtendedAnsiSQL=1;\"\r\n connection_url = engine.URL.create( \"access+pyodbc\",\r\n query = { \"odbc_connect\": connection_string } )\r\n global __engine, __connection\r\n __engine = create_engine( connection_url )\r\n __connection = __engine.connect()\r\n return __connection\r\n \r\n \r\n def getConnection():\r\n global __connection\r\n return __connection\r\n \r\n \r\n def getSession():\r\n global __engine, __session\r\n if not __engine:\r\n raise Exception( \"Connection not opened yet.\" )\r\n \r\n if __session:\r\n return __session\r\n \r\n SessionObject = sessionmaker( bind = __engine )\r\n __session = SessionObject()\r\n return __session\r\n \r\n \r\n def dump_tables():\r\n getEngine()\r\n session = getSession()\r\n # Dump contents of table 'FileTp1'\r\n for rec in session.query( FileTp1 ).all():\r\n print( rec )\r\n\r\n return\r\n \r\n \r\n if __name__ == '__main__':\r\n dump_tables()\r\n \r\n",
"bugtrack_url": null,
"license": "",
"summary": "Convert ODBC schema to ORM",
"version": "1.0.7",
"split_keywords": [
"odbc",
"sqlalchemu",
"orm"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "42775d6f9a84bef9725d325d6bf0d358cbc783dad7ca64317e44e93e138384c8",
"md5": "e30ffdac00af4f4be5b7db85184e5e53",
"sha256": "759a5c8c5bee3db14617509ffcd0047071f2bfc5721f685b8c14939904b7828f"
},
"downloads": -1,
"filename": "odbc2orm-1.0.7-cp38-cp38-win32.whl",
"has_sig": false,
"md5_digest": "e30ffdac00af4f4be5b7db85184e5e53",
"packagetype": "bdist_wheel",
"python_version": "cp38",
"requires_python": ">=3.6",
"size": 12071,
"upload_time": "2023-04-17T04:17:15",
"upload_time_iso_8601": "2023-04-17T04:17:15.587093Z",
"url": "https://files.pythonhosted.org/packages/42/77/5d6f9a84bef9725d325d6bf0d358cbc783dad7ca64317e44e93e138384c8/odbc2orm-1.0.7-cp38-cp38-win32.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "9a58f82144e906af78d198c9d34f5bc0a6cfdebf4f0544885e3fab54c8512c62",
"md5": "68c2eab905da4734dc2158652123f194",
"sha256": "e4707b50e4d9ce118a350754b3b433e4b03220d697e9609c2d4eae7a121be208"
},
"downloads": -1,
"filename": "odbc2orm-1.0.7.tar.gz",
"has_sig": false,
"md5_digest": "68c2eab905da4734dc2158652123f194",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.6",
"size": 11000,
"upload_time": "2023-04-17T04:17:17",
"upload_time_iso_8601": "2023-04-17T04:17:17.188247Z",
"url": "https://files.pythonhosted.org/packages/9a/58/f82144e906af78d198c9d34f5bc0a6cfdebf4f0544885e3fab54c8512c62/odbc2orm-1.0.7.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-04-17 04:17:17",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "pe2mbs",
"github_project": "odbc2orm",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [],
"lcname": "odbc2orm"
}
JSON
