Dowemo
0 0 0 0


Question:

I have a CSV file and want to generate dumps of the data for sqlite, mysql, postgres, oracle, and mssql.

Is there a common API (ideally Python based) to do this?

I could use an ORM to insert the data into each database and then export dumps, however that would require installing each database. It also seems a waste of resources - these CSV files are BIG.

I am wary of trying to craft the SQL myself because of the variations with each database. Ideally someone has already done this hard work, but I haven't found it yet.


Best Answer:


SQLAlchemy is a database library that (as well as ORM functionality) supports SQL generation in the dialects of the all the different databases you mention (and more).

In normal use, you could create a SQL expression / instruction (using a schema.Table object), create a database engine, and then bind the instruction to the engine, to generate the SQL.

However, the engine is not strictly necessary; the dialects each have a compiler that can generate the SQL without a connection; the only caveat being that you need to stop it from generating bind parameters as it does by default:

from sqlalchemy.sql import expression, compiler
from sqlalchemy import schema, types
import csv
# example for mssql
from sqlalchemy.dialects.mssql import base
dialect = base.dialect()
compiler_cls = dialect.statement_compiler
class NonBindingSQLCompiler(compiler_cls):
    def _create_crud_bind_param(self, col, value, required=False):
        # Don't do what we're called; return a literal value rather than binding
        return self.render_literal_value(value, col.type)
recipe_table = schema.Table("recipe", schema.MetaData(), schema.Column("name", types.String(50), primary_key=True), schema.Column("culture", types.String(50)))
for row in [{"name": "fudge", "culture": "america"}]: # csv.DictReader(open("x.csv", "r")):
    insert = expression.insert(recipe_table, row, inline=True)
    c = NonBindingSQLCompiler(dialect, insert)
    c.compile()
    sql = str(c)
    print sql

The above example actually works; it assumes you know the target database table schema; it should be easily adaptable to import from a CSV and generate for multiple target database dialects.




Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs