Using SQLAlchemy to build sql files (without execution)

[Maxim Khitrov]

Hello all,

I'm working on a project for which I need to create sql files (the
sort that you get from a database dump) without actually connecting to
any database. The resulting code must be compatible with MySQL.

My first inclination was to write a bunch of functions that contain
sql code templates and substitute dynamic values where needed. I then
came across SQLAlchemy and wanted to see if could use it to make this
task a little easier.

So far I've been reading the documentation on meta data and running
some tests. My guess is that I need to define the statements and
compile them with MySQLDialect. This, however, doesn't seem to be
working. I tried calling compile on a Table object with dialect set to
MySQLDialect(). When the result is printed there is nothing there
(probably because I have no idea what I'm doing :)).

Can anyone provide some tips on whether what I'm trying to do is
possible, and if so, what is the best way to go about it? Basically,
the output files will be a combination of create database, create
table, insert, and update statements. I should also mention that I'm
using Python 2.6 on Windows. The latest stable MySQL-Python extension
doesn't support 2.6, and there doesn't seem to be a test version
available for Windows.

Even if the database API was available, there is no database for me to
connect to. The goal is to generate these files on the local machine
without depending on any external resources. Eventually, it may be
possible to execute this code directly on the database server, which
is why I think using SQLAlchemy from the beginning would make that
transition much easier. Thanks for any help.

- Max

[Yassen Damyanov]

Max,

I am an SA newbie so you should not take my words too seriously.

So here's what I think:

First, the only way to know and be sure that your code runs fine is to
run it against a real database server. MySQL is not that hard to set
up. (If you need help for that, let me know, I can help.)

Second, if you mean that you want to generate the DDL (the tables
creation statements), then how about having SA run against a real
MySQL server and logging the SQL activity? (I guess this may sound
ugly but it will work with sure.)

If you want to generate anything more than simply DDL, it looks to me
that a different approach will do a better job.

(Advice from real experts is more than appreciated.)
Yassen

[a...@svilendobrev.com]

you can use echo=True to metadata and/or logging to some extent.
there was also some recipe about how to print some query's actual sql
without issuing it, see recipes section at the site.
but i'm not sure u can really do all you want without having a
responding DB-api (which will at some point require a server).
try, YMMV.

svil
www.svilendobrev.com

[Yassen Damyanov]

On Mar 31, 2009, Maxim Khitrov <mkhi...@gmail.com> wrote:
> I should also mention that I'm
> using Python 2.6 on Windows. The latest stable MySQL-Python extension
> doesn't support 2.6, and there doesn't seem to be a test version
> available for Windows.

The 1.2.2 Windows version of mysql-python should work flawlessly under
Python 2.6. (I haven't tested that however, my Python is 2.5.)
Y.

[Maxim Khitrov]

Yes, you're right. I searched through the forums and found an
installation package for 2.6 that someone else put together.

If using SQLAlchemy to create these dump files directly is not
possible, what about using reflection to load the schema for a given
database, recreating that schema in a temporary database, loading
required data, and then generating the SQL dump?

I can do the first part of that like so:

engine = create_engine('mysql://root:<password>@localhost/caffeine_gum2')
meta = MetaData()
meta.reflect(bind=engine)

Once I have the metadata, how can I create a temporary database and
reload the table structure there? I suppose I'll need to use some
other tool for generating the dump once the data is inserted, but
that's fine. As long as I'm not modifying the live data I'll be happy.

- Max

[a...@svilendobrev.com]

for example see
copyall.py and copydata.py in
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/metadata/

[Yassen Damyanov]

On Thu, Apr 2, 2009, Maxim Khitrov <mkhi...@gmail.com> wrote:
>
> If using SQLAlchemy to create these dump files directly is not
> possible, what about using reflection to load the schema for a given
> database, recreating that schema in a temporary database, loading
> required data, and then generating the SQL dump?
>
> I can do the first part of that like so:
>
> engine = create_engine('mysql://root:<password>@localhost/caffeine_gum2')
> meta = MetaData()
> meta.reflect(bind=engine)

Sounds good (although I am not familiar with reflection in SA :( )

> Once I have the metadata, how can I create a temporary database and
> reload the table structure there? I suppose I'll need to use some
> other tool for generating the dump once the data is inserted, but
> that's fine. As long as I'm not modifying the live data I'll be happy.

How about 'mysqldump database' ? You can use the '--compatible' option
to generate an SQL dump that is compatible with mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb.
Y.

[Maxim Khitrov]

I'll need to use mysqldump on the temporary database, but there I was
asking about creating that database from metadata. The following seems
to work well:

engine = create_engine(dsn)

url = engine.url
url.database = 'temp'

meta = MetaData()
meta.reflect(bind=engine)

conn = engine.connect()
conn.execute('DROP DATABASE IF EXISTS temp')
conn.execute('CREATE DATABASE temp')
conn.close()

engine = create_engine(url)

meta.bind = engine
meta.create_all()

- Max