How to execute user defined SQL query at run time using SQLAlchemy?

Question

I have a use case implementation for which I am trying to identify the solution, the implementation is as follows :

User defines the SQL server connection parameters like DATABASE_TYPE ( like MySQL, pgsql, sqlite or any db supported by SQLAlchemy), HOST, PORT, SCHEMA_NAME, USER_NAME and PASSWORD
User then does the TEST CONNECTION to validate the connection parameters provided, and provides SUCCESS and FAILURE feedback
On SUCCESS, user will type in the sql query ( following SQLAlchemy's SQL Expression Language more details here ) the which needs to be executed.

If you notice above all the parameters for SQLAlchemy to work are user defined at run time, I am trying to implement the function in python which consumes the above connection parameters and sql query, executes the query on successful connection and returns the query result. Any point of direction or help on this would be great.

Thanks

Why not simply do this at the >>> Python prompt? If everything is simply access to SQLAlchemy, why write any additional code? — S.Lott
– S.Lott, Commented Feb 1, 2012 at 13:26
@S.Lott Well user submits the connection parameters and sql query (as a string) via a web form, and using SQLAlchemy is a need so as to abstract the query format. — Zakiullah Khan
– Zakiullah Khan, Commented Feb 1, 2012 at 13:33
Why have a web form for something like this? It doesn't seem like the web form is really doing any useful work? — S.Lott
– S.Lott, Commented Feb 1, 2012 at 14:00
Well @S.Lott thats an application feature. Cant say much for now. — Zakiullah Khan
– Zakiullah Khan, Commented Feb 2, 2012 at 4:55
Not doing anything useful is a feature? Interesting use of the word. — S.Lott
– S.Lott, Commented Feb 2, 2012 at 10:43

Bogdan · Accepted Answer · 2012-02-01 13:44:07Z

1

I'm having something like this as part of a larger project, so far only for sqlite and postgres but this can be generalized.

For start if you expect the user to know DATABASE_TYPE ( like MySQL, pgsql, sqlite or any db supported by SQLAlchemy), HOST, PORT, SCHEMA_NAME, USER_NAME and PASSWORD you could just ask for the DB URL to make your life a little easier.

After that you can test the connection by:

    from sqlalchemy import create_engine
    try:
        engine = create_engine(url)           
        connection = engine.connect()
        connection.close()
    except Exception, e:
        LOGGER.exception(e)
        raise MyCusomException('Could not initialize db. Invalid URL.')

Cusom sql queries can be executed using the text() construct as shown here.

answered Feb 1, 2012 at 13:44

Bogdan

8,3166 gold badges51 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zakiullah Khan Over a year ago

Thanks @Bogdan for the direction, it does solve my problem. Thanks again.

Zakiullah Khan Over a year ago

@Bodgan Wanted to check if you could help on guiding how to serialize dynamic query serialization to JSON or pickle ? Thanks.

Bogdan Over a year ago

I'm not sure I fully understand what you are trying to do. Maybe try asking another question on that matter with some examples of what you want/tried and I'll try to help if possible.

Collectives™ on Stack Overflow

How to execute user defined SQL query at run time using SQLAlchemy?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related