Convert ODBC schema to ORM
Project description
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
> 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;
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.
- ordinal; 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()
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for odbc2orm-1.0.6-cp38-cp38-win32.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 05115d22325b3eb71dc83fdaa4ab3e82c947d1ca5b58e35a1ee3fd29d3e6c7a0 |
|
MD5 | cc633bd8555b931f0bf216241f2a4b5d |
|
BLAKE2b-256 | 3cde945f4a73955cdc77e3975a04c5433463308f4ae8d4c246abff0d7a086752 |