[sqlalchemy] Re: autoload'ing metadata
MSSQL is case-sensitive, and wants to see queries to INFORMATION_SCHEMA in
UPPER CASE.
See mssql.py.uppercase_table() for the gory details, or rather, THE GORY
DETAILS ;-)
On 7/27/07, Christophe de VIENNE <[EMAIL PROTECTED]> wrote:
>
>
> Hi svil,
>
> Still no luck. I don't know if the information_schema module is
> supposed to work well with pymssql. Anyway :
>
> Traceback (most recent call last):
> File "autoload.py", line 233, in ?
> autoloader = AutoLoader( engine)
> File "autoload.py", line 100, in __init__
> me.table_names = engine.execute( sqltext)
> File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
> line 773, in execute
> return connection.execute(statement, *multiparams, **params)
> File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
> line 517, in execute
> return Connection.executors[c](self, object, *multiparams, **params)
> File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
> line 557, in execute_clauseelement
> return self.execute_compiled(elem.compile(dialect=self.dialect,
> parameters=param), *multiparams, **params)
> File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
> line 568, in execute_compiled
> self._execute_raw(context)
> File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
> line 581, in _execute_raw
> self._execute(context)
> File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
> line 599, in _execute
> raise exceptions.SQLError(context.statement, context.parameters, e)
> sqlalchemy.exceptions.SQLError: (DatabaseError) internal error (SQL
> Server message 208, severity 16, state 1, line 1:
> Invalid object name 'information_schema.tables'.
> DB-Lib error message 20018, severity 5:
> General SQL Server error: Check messages from the SQL Server.
> ): SQL Server message 208, severity 16, state 1, line 1:
> Invalid object name 'information_schema.tables'.
> DB-Lib error message 20018, severity 5:
> General SQL Server error: Check messages from the SQL Server.
> 'SELECT tables_a3c4.table_name, tables_a3c4.table_schema \nFROM
> information_schema.tables AS tables_a3c4 \nWHERE
> tables_a3c4.table_schema = %(tables_table_schema)s'
> {'tables_table_schema': 'sf_tmp'}
>
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
Hi, >Still no luck. I don't know if the information_schema module is >supposed to work well with pymssql. Anyway : > > It works ok on Windows. Have a go at trying an information_schema query directly in PyMSSQL, without using SA at all. That should settle the matter. Paul --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
Hi svil,
Still no luck. I don't know if the information_schema module is
supposed to work well with pymssql. Anyway :
Traceback (most recent call last):
File "autoload.py", line 233, in ?
autoloader = AutoLoader( engine)
File "autoload.py", line 100, in __init__
me.table_names = engine.execute( sqltext)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 773, in execute
return connection.execute(statement, *multiparams, **params)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 517, in execute
return Connection.executors[c](self, object, *multiparams, **params)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 557, in execute_clauseelement
return self.execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), *multiparams, **params)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 568, in execute_compiled
self._execute_raw(context)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 581, in _execute_raw
self._execute(context)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) internal error (SQL
Server message 208, severity 16, state 1, line 1:
Invalid object name 'information_schema.tables'.
DB-Lib error message 20018, severity 5:
General SQL Server error: Check messages from the SQL Server.
): SQL Server message 208, severity 16, state 1, line 1:
Invalid object name 'information_schema.tables'.
DB-Lib error message 20018, severity 5:
General SQL Server error: Check messages from the SQL Server.
'SELECT tables_a3c4.table_name, tables_a3c4.table_schema \nFROM
information_schema.tables AS tables_a3c4 \nWHERE
tables_a3c4.table_schema = %(tables_table_schema)s'
{'tables_table_schema': 'sf_tmp'}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
On Friday 27 July 2007 12:44:49 Christophe de VIENNE wrote:
> 2007/7/26, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:
> > noone wanting to try autoload'ing nor metadatadiff? i am
> > surprised.. Christophe, u can at least try how much autoload.py
> > works like your autocode2 - i got lost with 'schema' vs 'dbname'
> > - and/or add mysql support (;-)
>
> I tried to run it on a mssql db (Although I'd prefer to test it on
> a mysql db to see the actual differences from autocode2), but I got
> some errors :
well, u have put only mssql in your autocode2 - which i ripped.
i've no idea about mysql - and about mssql either.
up to you if u wanna hack it further, and find out what should be the
way there.
> Traceback (most recent call last):
> ...
> 'sqlalchemy.databases.mssql.MSSQLDialect_pymssql'>): None 'SELECT
> tables_77bf.table_name, tables_77bf.table_schema \nFROM
> information_schema.tables AS tables_77bf \nWHERE
> tables_77bf.table_schema = %(tables_table_schema)s'
> {'tables_table_schema':
> 0xb78a7f0c>}
hmmm
replace the line 91
schema = engine.dialect
with
schema = engine.url.database
this might be equivalent to your old code (if works at all).
i'm not sure what should be there anyway... and have nowhere to test
now.
ciao
svil
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
2007/7/26, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:
> noone wanting to try autoload'ing nor metadatadiff? i am surprised..
> Christophe, u can at least try how much autoload.py works like your
> autocode2 - i got lost with 'schema' vs 'dbname' - and/or add mysql
> support (;-)
I tried to run it on a mssql db (Although I'd prefer to test it on a
mysql db to see the actual differences from autocode2), but I got some
errors :
Traceback (most recent call last):
File "autoload.py", line 233, in ?
autoloader = AutoLoader( engine)
File "autoload.py", line 100, in __init__
me.table_names = engine.execute( sqltext)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 773, in execute
return connection.execute(statement, *multiparams, **params)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 517, in execute
return Connection.executors[c](self, object, *multiparams, **params)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 557, in execute_clauseelement
return self.execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), *multiparams, **params)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 568, in execute_compiled
self._execute_raw(context)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 581, in _execute_raw
self._execute(context)
File "/home/cdevienne/prog/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) internal error (do not
know how to handle type ): None 'SELECT
tables_77bf.table_name, tables_77bf.table_schema \nFROM
information_schema.tables AS tables_77bf \nWHERE
tables_77bf.table_schema = %(tables_table_schema)s'
{'tables_table_schema':
}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
noone wanting to try autoload'ing nor metadatadiff? i am surprised.. Christophe, u can at least try how much autoload.py works like your autocode2 - i got lost with 'schema' vs 'dbname' - and/or add mysql support (;-) http://dbcook.svn.sourceforge.net/viewvc/*checkout*/dbcook/trunk/autoload.py http://dbcook.svn.sourceforge.net/viewvc/*checkout*/dbcook/trunk/metadatadiff.py requires dbcook only for the test have fun --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
On Thursday 26 July 2007 11:37:08 Marco Mariani wrote: > [EMAIL PROTECTED] ha scritto: > > here some theory on comparing data trees, in order to produce the > > changeset edit scripts. > > http://www.pri.univie.ac.at/Publications/2005/Eder_DAWAK2005_A_Tr > >ee_Comparison_Approach_to_Detect.pdf > > The complete title of the paper is "A Tree Comparison Approach To > Detect Changes in Data Warehouse Structures". > > "data warehouse" is the key concept. > > > of course full automation is not possible and not needed - but > > why not do maximum effect/help with minimum resources? > > I've not read it, but what is working for data warehouse could fail > miserably in a normalized database. sure. there are graphs and not just trees. Apart of that, same thing, nodes and edges. u can try the metadatadiff.py, there's lots of node-types to add/describe but IMO the idea is there. or u can keep doing it by hand. choice is yours. Actualy, i'm the worst one to develop this - i have no enough experience with sql and db-admining in general, nor i know _all_ internals of SA. But hey... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
[EMAIL PROTECTED] ha scritto: > here some theory on comparing data trees, in order to produce the > changeset edit scripts. > http://www.pri.univie.ac.at/Publications/2005/Eder_DAWAK2005_A_Tree_Comparison_Approach_to_Detect.pdf > The complete title of the paper is "A Tree Comparison Approach To Detect Changes in Data Warehouse Structures". "data warehouse" is the key concept. > of course full automation is not possible and not needed - but why not > do maximum effect/help with minimum resources? > I've not read it, but what is working for data warehouse could fail miserably in a normalized database. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
okay. first cut of metadatadiff:
http://dbcook.svn.sourceforge.net/viewvc/*checkout*/dbcook/trunk/metadatadiff.py
... test changes:
class Address( o2r.Base):
size = Text() #change type
place = Text2() #change type-details
class Employee( o2r.Base): #inheritance gone - was off Person
jobe = Text() #change name
class Employee2( Person): #new class
lazy = Text()
$ python metadatadiff.py
=
DiffMetaData : None
tables ins [Table('Employee2',.)]
tables sub [, ]
DiffTable : Employee
columns ins [Column( 'jobe', String, )]
columns del [Column( 'job', String, )]
columns sub []
DiffColumn : db_id
foreign_keys del [ForeignKey('Person.db_id')]
DiffTable : Address
columns sub [, ]
DiffColumn : place
data {'type': (String(length=None,convert_unicode=False),
String(length=30,convert_unicode=False))}
DiffColumn : size
data {'type': (Integer(),
String(length=None,convert_unicode=False))}
not too bad...
ciao
svil
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
here some theory on comparing data trees, in order to produce the changeset edit scripts. http://www.pri.univie.ac.at/Publications/2005/Eder_DAWAK2005_A_Tree_Comparison_Approach_to_Detect.pdf of course full automation is not possible and not needed - but why not do maximum effect/help with minimum resources? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
another version, separated autoload from code-generation, which is now the __main__ test. http://dbcook.svn.sourceforge.net/viewvc/*checkout*/dbcook/trunk/autoload.py now it is possible to do something like: $ python autoload.py postgres://[EMAIL PROTECTED]/db1 | python - sqlite:///db2 copying the structure of input db1 database into the output db2. ciao svilen > this is along the recent threads about metadata consistency between > code and DB, and the DB-migration. Both these require a full > metadata reflection from database. > > Here a version of autocode.py, hacked for couple of hours. > It has more systematic approach, replaces back column types with SA > ones, has sqlite and postgress, and is somewhat simpler but more > dense. Output also looks nicer (nested identation). Not tested for > mssql. > > my idea is to use this metadata-reflection as starting point > towards model-migration technology or framework or whatever. > > one way is to put all metadata-reflection in the dialects > themselves. Maybe there should be reflect_metadata() method, which > will extract all tables/names, indexes, etc. This is what i like, > although it means hacking 10 files instead of one. But it would be > more easier/consistent on the long run. > > Another way is to pull all reflection stuff out of dialects, or at > least separate it somehow. > > anyway. > > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode#autoload2 >codeorAutoCode3 > > using some metadata howto from: > http://sqlzoo.cn/howto/source/z.dir/tip137084/i12meta.xml > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
