DB inspection with Elixir
One of the things I do at my day job is work with both legacy databases and databases created on MS SQL Server. Using Python with these systems is a breeze thanks to one of my all time favorite 3rd party modules: Elixir.
Elixir provides a low barrier to entry into the world of SQLAlchemy (a powerful Object Relational Mapper for Python, and a topic for another day…). The following is an example of how to inspect a database table and generate an easy to use class:
1 import elixir 2 3 elixir.metadata.bind = "mssql://YOUR_ODBC_ALIAS" 4 5 class MyTable(elixir.Entity): 6 elixir.using_options(tablename="MyTableName",autoload=True) 7 8 elixir.setup_all() 9 10 # Create your instance 11 my_table = MyTable(name="James",age=30) 12 # Save it 13 my_table.save() 14 15 # It's important to remember to commit your changes 16 elixir.session.commit() 17 18 # Check out your new recordset 19 record_set = MyTable.query.all()
That’s all there is to it. Elixir/SQLAlchemy will use the appropriate ODBC connector installed (in my case it’s pyodbc). Now you can use that class to create, read, update or delete data in that table.
Elixir is not just for ODBC database access. You can use it with any supported SQLAlchemy database
While using an ORM is not a one-size fits all type of thing, this third party module has saved me a lot of headaches, and has drastically cut down on writing redundant, mundane SQL. I highly recommend it!
If you’re interested in learning more, checkout the tutorial.
Be more productive. Check out Elixir!