how to run a .sql file on different database in python

Question

So this is my requirement. I've some use python module to execute .sql files on different databases such as oracle, MSSQL, db2, etc. on different platforms such linux and windows. In my requirement i can't open the sql file and run each command. I've to run .sql file as whole. I was reading about sql alchemy but it seems as it has to execute each statement. So is there is any module to run the complete .sql file

PS: - I've .sql file with respect to each database, i.e. if I've abc.sql for oracle then every statement is compatible to run on oracle database and if this abc.sql file is not supposed to run on MSSQL or DB2, my program will not execute it on these database.

Yes I'm able to run the native tools. I'm also thinking in programing in terms of native tools only, however, I've some doubts regarding the native tools compatibility with different versions of same database. for instance my current program works for oracle 11g but not for oracle 8.1 . Also after some research i found that MSSQL require different native client for its different DB versions. Do you have any solution in this area ? — Hemant
– Hemant, Commented Sep 19, 2013 at 9:10
Do you generate such .sql files? If so, then use first line as it is in unix environment but with SQL comment (--!/usr/bin/program_that_handles_my_file), or better add special comment that shows for which version is this file. — Michał Niklas
– Michał Niklas, Commented Sep 19, 2013 at 10:27
Yes we generate such SQL file, however I didn't get what is the benefit of writing such comment. Actually we create this information about .sql file in xml format and my program store this information in dictionary. — Hemant
– Hemant, Commented Sep 19, 2013 at 12:01
Of course. I work with different versions of PostgreSQL. I have psql for the newest version, psql91 for 9.1, psql92 for 9.2 etc. Just prepare environment by making proper links, scripts, batches etc. — Michał Niklas
– Michał Niklas, Commented Sep 20, 2013 at 6:29

Michał Niklas · Accepted Answer · 2013-09-20 06:27:03Z

No. There is standard SQL language, but this standard says only how SELECT, INSERT etc should work. There is nothing about .sql files. This way each DBMS vendor has his own ways of working with such files. In PostgreSQL you can easily run psql command line programm with your file as input. In Oracle world you can try to do the same using sqlplus but of course such .sql files will vary. In PostgreSQL you should set encoding of input file using non-standard SQL command that other vendors will report as error. For such things Oracle uses environment settings. With Oracle your .sql file for sqlplus must end with COMMIT; EXIT; etc. Even datetime string literals are different for each vendor. MS SQL uses {ts '...'} which will not work with Informix, PostgreSQL nor Oracle.

Shortly: it seems impossible to do such program for each database.

All you can do is to invite additional layer that will change your standard input file (add some header and footer, convert datetime literals etc). Then such prepared file can be run against command line tools given by database vendor, or by your specialized program able to execute such converted file.

EDIT:

It seems that you have different files for different databases, and even for different database versions. You can also use native programs that are able to run .sql file. So the only problem is to detect database and database version and execute proper file using proper native client. This is code in Jython (I often use JDBC, but you can use DB-API and Python db drivers):

def get_db_version(db):
    dbname = ''
    ver = ''
    c = db.createStatement()
    try:
        rs = c.executeQuery("SELECT FIRST 1 DBINFO('version','full') FROM systables")
        dbname = 'informix'
        # IBM Informix Dynamic Server Version 11.50.FC4
    except:
        try:
            rs = c.executeQuery("SELECT * FROM v$version WHERE banner LIKE 'Oracle%'")
            dbname = 'oracle'
            # Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
        except:
            try:
                rs = c.executeQuery("SELECT version()")
                dbname = 'postgresql'
                # PostgreSQL 9.2.4 on ..., 64-bit
                # PostgreSQL 9.3.0 on ..., 64-bit
            except:
                raise
    if dbname:
        while (rs.next()):
            ver = rs.getString(1)
    return dbname, ver


def select_sql_app_and_file():
    import_app = None
    ver_postfix = ''
    dbname, ver = get_db_version(create_db_connection())
    if dbname == 'postgresql':
        import_app = 'psql'
        if 'PostgreSQL 9.2' in ver:
            import_app = 'psql92'
            ver_postfix = '92'
    elif dbname == 'oracle':
        import_app = 'sqlplus'
        if 'Release 11.' in ver:
            import_app = 'sqlplus11'
            ver_postfix = '11'
    # ...
    sql_file_name = 'import_' + dbname + ver_postfix + '.sql'
    return import_app, sql_file_name


def run_sql_file():
    import_app, sql_file_name = select_sql_app_and_file()
    if import_app:
        execute_import_app(import_app, sql_file_name)

Collectives™ on Stack Overflow

how to run a .sql file on different database in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related