gmane.comp.python.sqlobject

Sam's Lists | 25 Mar 21:22

Finding the names of the tables...

I have a side project I'd like to write.... A small utilitl that will traverse my database automatically and spit out every table, row, and column in a really readable format. (I'll probably use yaml). Phpmyadmin does something similar, except you have to use the browser....I want this to run daily without the browser.

I guess you could also look at it as my own mysqldump. Except mysqldump only has an xml option, no yaml or anything that I consider easily readable.

I'd of course like to rely on sqlobject as much as possible for this endeavor.

My first silly question is how can I, using sqlobject, find the names of all the tables in my database.

(A related question...can I find the names of all the databases as well?)

Thanks!

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

headers

Oleg Broytmann | 25 Mar 22:00

Re: Finding the names of the tables...

On Tue, Mar 25, 2008 at 05:24:10PM -0300, Sam's Lists wrote:
> My first silly question is how can I, using  sqlobject, find the names of
> all the tables in my database.
> 
> (A related question...can I find the names of all the databases as well?)

   There is no way in SQLObject. SO assumes you know your database and
tables. You can draw the scheme for a table from DB but you have to know
the table's name in advance.

   Of course you can add such feature to SO. The query in MySQL is
SHOW TABLES, in SQLite it is
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
and in Postgres it is something like SELECT c.relname FROM pg_class c,
pg_type t WHERE c.reltype = t.oid AND t.typname = 'table';

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/

headers

Sam's Lists | 13 Apr 00:31

Re: Finding the names of the tables...

Oleg...

Thanks for this! I'm just getting back to it now...

(For those that don't remember the original message, I want to dump my whole database as text or yaml or json or whatever....basically I'm writing my own mysqldump).

So you've given me step one and it works well. Thank you.

tables = connection.queryAll('show tables')
for table in tables:
    print "\t", table[0]

Now I know step three is something like this:
        recdict = record.sqlmeta.asDict()

But I can't figure out how to go from step one to step three. Given the table name as a string, how do I convert it to an object where sqlobject goes and creates an object based on the info in the database?

Thanks again!

On Tue, Mar 25, 2008 at 6:00 PM, Oleg Broytmann <phd <at> phd.pp.ru> wrote:

On Tue, Mar 25, 2008 at 05:24:10PM -0300, Sam's Lists wrote:
> My first silly question is how can I, using sqlobject, find the names of
> all the tables in my database.
>
> (A related question...can I find the names of all the databases as well?)

There is no way in SQLObject. SO assumes you know your database and
tables. You can draw the scheme for a table from DB but you have to know
the table's name in advance.

Of course you can add such feature to SO. The query in MySQL is
SHOW TABLES, in SQLite it is
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
and in Postgres it is something like SELECT c.relname FROM pg_class c,
pg_type t WHERE c.reltype = t.oid AND t.typname = 'table';

Oleg.
--
Oleg Broytmann http://phd.pp.ru/ phd <at> phd.pp.ru
Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference 
Don't miss this year's exciting event. There's still time to save $100. 
Use priority code J8TL2D2. 
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

headers

Oleg Broytmann | 13 Apr 17:27

Re: Finding the names of the tables...

On Sat, Apr 12, 2008 at 07:31:07PM -0300, Sam's Lists wrote:
> So you've given me step one and it works well.  Thank you.
> 
> tables = connection.queryAll('show tables')
> for table in tables:
>     print "\t", table[0]
> 
> Now I know step three is something like this:
>         recdict = record.sqlmeta.asDict()
> 
> But I can't figure out how to go from step one to step three.  Given the
> table name as a string, how do I convert it to an object where sqlobject
> goes and creates an object based on the info in the database?

   Something like this:

tableClasses = {}
for table in tables:
    tname = table[0]
    class Table(SQLObject):
        class sqlmeta:
            table = tname
            fromDatabase = True
    tableClasses[tname] = Table

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference 
Don't miss this year's exciting event. There's still time to save $100. 
Use priority code J8TL2D2. 
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone

headers

Sam's Lists | 14 Apr 05:27

Re: Finding the names of the tables...

Oleg...

Thanks for this....

I tried your method, but I think you can't dynamically create classes that way. I had some success with this:

table_classes = []
i = 0
for table in tables:
    print i, "-", table[0]
    y = new.classobj('sqlmeta', (), {'table': table[0], 'fromDatabase': True})
    x = new.classobj(str(table[0]),(SQLObject,), {'sqlmeta': y})
    for z in x.sqlmeta.asDict():
        print z
    table_classes.append(x)
    i += 1

But it's still not completely right....I get this error:
Traceback (most recent call last):
File "samdump.py", line 32, in <module>
    for z in x.sqlmeta.asDict():
TypeError: unbound method asDict() must be called with sqlmeta instance as first argument (got nothing instead)

Anyone know what I'm doing wrong? I sort of guessed at how to do it...if you can't tell. :)

Thanks!

On Sun, Apr 13, 2008 at 12:27 PM, Oleg Broytmann <phd <at> phd.pp.ru> wrote:

On Sat, Apr 12, 2008 at 07:31:07PM -0300, Sam's Lists wrote:
> So you've given me step one and it works well. Thank you.
>
> tables = connection.queryAll('show tables')
> for table in tables:
> print "\t", table[0]
>
> Now I know step three is something like this:
> recdict = record.sqlmeta.asDict()
>
> But I can't figure out how to go from step one to step three. Given the
> table name as a string, how do I convert it to an object where sqlobject
> goes and creates an object based on the info in the database?

Something like this:

tableClasses = {}
for table in tables:
tname = table[0]
class Table(SQLObject):
class sqlmeta:
table = tname
fromDatabase = True
tableClasses[tname] = Table

Oleg.
--
Oleg Broytmann http://phd.pp.ru/ phd <at> phd.pp.ru
Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------

This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference 
Don't miss this year's exciting event. There's still time to save $100. 
Use priority code J8TL2D2. 
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

headers

Oleg Broytmann | 14 Apr 12:38

Re: Finding the names of the tables...

On Mon, Apr 14, 2008 at 12:27:55AM -0300, Sam's Lists wrote:
> Traceback (most recent call last):
>   File "samdump.py", line 32, in <module>
>     for z in x.sqlmeta.asDict():
> TypeError: unbound method asDict() must be called with sqlmeta instance as
> first argument (got nothing instead)

   It seems metaclass didn't do its work right by setting up sqlmeta.
I don't know if a call to new.classobj() calls metaclass.

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference 
Don't miss this year's exciting event. There's still time to save $100. 
Use priority code J8TL2D2. 
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone