[storm] How to CREATE DATABASE using Storm

[Jamu Kakar]

Hi Mario,

On Thu, Apr 22, 2010 at 12:37 AM, Mario Zito <mazito at analyte.com> wrote:
> I'd like to  to connect to a db server, create a
> database, and then start using it. However, it appears that the
> Storm api assumes the existence of a database to connect to, before you
> can execute some sql. I'm not able to connect to the server without a
> database specified.
>
> So, I tried:
>
>     db= create_database("postgres://postgres:password@localhost/postgres")
>     cx= db.connect()
>     cx.execute("CREATE DATABASE demo_1")
> But I get:
>
>   psycopg2.InternalError: CREATE DATABASE cannot run inside a transaction
> block
>
> I tried some alternatives, like using raw_execute(), but have no success. Is
> there a solution to this using Storm ?
> I know I can resolve this starting a child process and executing pgsql with
> a sql script, but would like a better solution :-)

I don't know of a way to do this with Storm.  Storm issues BEGIN
statements transparently, so any query you try to run will result in
a transaction being created.  You can probably work around the
problem with something like the following (untested) code:

database = create_database("postgres://localhost/postgres")
connection = database.raw_connect()

At this point 'connection' is a connection object retrieved from
psycopg2 (ie: not a storm.database.Connection instance).  You should
be able to use this raw connection to do what you want:

cursor = connection.cursor()
cursor.execute("CREATE DATABASE demo_1")
cursor.close()
connection.commit()
connection.close()

This is a basically hack, though.  You'd only be using Storm to
establish a connection with the database in this case... it may
easier to use the DB-API [1] directly to create your database and
then switch over to using Storm to work with it.

Thanks,
J.

[1] http://www.python.org/dev/peps/pep-0249/