This message concerns using sqlAlchemy for schema based traversal and
manipulation.
It is the result of a project to transfer data from on database to another,
where objects
refer to each other, and must be copied to new rows in the target db, and
have all the
foreign references updated in the new db.
I'm using the 1.0 release of sqlalchemy on python 2.7.9 (ubuntu 15), btw.
I've gotten thru a very large percentage of the project, but see some places
where a few "shortcuts" could be supplied that would shorten the code
and make programming it a bit simpler... just in case anyone is interested.
dynamic lists vs. the declarative approach:
I needed to form the list of variables and values to insert into the target
db at run time. I had success here:
vals = {}
from_tab = meta1.tables[table_name]
q = select([from_tab]).where(from_tab.c.id == from_id)
result = con1.execute(q)
res_colnames = result.keys()
res_row = result.first()
for colname, val in zip(res_colnames, res_row) :
...
vals[colname] = val;
...
to_tab = meta2.tables[table_name]
ins = to_tab.insert().values(**vals) ## here use a map instead of
declarative code
result = self.con2.execute(ins)
The code above is a bit simplified from real life. the table_name is
supplied,
the meta1, con1, are all gotten from easily imaginable function call
results,
and so are meta2, con2 for the target db.
We can assume the from_id is the id in the "from" db, of the row in
the "from" table_name that we want to copy the data.
We fetch the row from the "from" db, and we (with some filtering, like
removing the id of the originating row) then insert the filtered values
into the "to" db's table of the same name. We will obtain the id of the
new row created, and set up a simple mapping, after the insert is
executed.
So much for the task description!
In the below, if you notice I'm missing something in the way of
knowledge, feel free to enlighten me!
A bit difficult is grabbing just certain columns in the select, given
that we have only a list of column names. That would be real nice
if such a method were available in the core API.
For example, a method for select whereby I could supply a simple
list of column names to fetch in the select would be very handy:
tablename = "user"
tab = meta1.tables[tablename]
collist = [ "id", "name", "address" ] ## just a few of many more
q = tab.select(colnames=collist)
In the meantime,
it is a bit simpler to just grab the contents of the whole row, and
sift thru the results for the columns actually needed.
Also, if I could get a column object from a table by name
it would really make life easier at times:
q = select([tab]).where(tab.colname(colnamevar) == col_var_val)
where a colname method would do the lookup for you, and return with
the equivalent of what tab.c.fixedcolname would give.
Another rough spot with the API is setting up a simple set of where
clauses for a select, given a map of column names vs. values.
if the map contained {"user": "cat", "city": "Gotham"} it would
be cool if we could get the desired select:
select * from table where user='cat' and city='Gotham';
via maybe something like this:
wheremap = {"user": "cat", "city": "Gotham"}
q = select([from_tab]).where_from_map(wheremap)
Another rough spot is getting a list of constraints. I note that
postgresql has sql to create constraints, and remove them, but
not to get a list of them. I do see the "\d <table-name>" command,
and you get a list of all the foreign keys and constraints that way,
all in a big block of text.... I'm sure that, without supporting sql syntax,
any further sqlalchemy constraint support will be very hard, if not
impossible,
to implement.
The fact that sqlalchemy does what it does has saved me a ton of time
writing
a full-blown SQL parser. Many congrats, thanks, ataboys, and kudu's!!!
murf
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
