[sqlalchemy] Introspecting sequences
I want* to get the current values of all the sequences in a database, or at
least the ones that are being used to autoincrement primary keys.
Can I find those with SQLAlchemy? I haven't been able to find them in
metadata after ``metadata.reflect()`` yet, or with ``reflection.Inspector``.
``metadata._sequences`` is empty even though schemas definitely exist in
the database (and I shouldn't rely on an underscore-preceded attribute
anyway). The closest I've been able to find is::
str(meta.tables['mytable'].c.id.server_default.arg)
nextval('mytable_id_seq'::regclass)
... but trying to scrape my sequence names out of strings like that feels
dangerous.
* - Reason: For DDLGenerator, I want to be able to dump SQLAlchemy
statements that duplicate a small database in SQLAlchemy terms. In this
case, for py.test fixtures - so you can work out your test database in live
SQL, or with a tool like rdbms_subsetter, and then generate a .py file that
fits nicely into your unit testing environment. So far so good, except I'm
inserting rows with their primary key values, but nothing is updating the
values of the sequences associated with them, so the next attempted insert
fails with a primary key violation.
Thanks,
--
- Catherine
http://catherinedevlin.blogspot.com
--
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.
[sqlalchemy] Generating models from data
Hi, everybody, Apologies for the self-promotion, but I just wrote up a way to automatically generate SQLAlchemy models automatically based on the (not-yet-relationalized) data, and I want to boast: ddlgenerator --inserts sqlalchemy rawdata.yaml (or .xml, .json, .html) I crave your bug reports! http://catherinedevlin.blogspot.com/2014/07/auto-generate-sqlalchemy-models.html https://pypi.python.org/pypi/ddlgenerator/ Thanks, -- - Catherine http://catherinedevlin.blogspot.com -- 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.
[sqlalchemy] capture SQL without executing
Hi! I'm looking for a way to capture the text of SQL that SQLAlchemy is
prepared to execute, without actually executing the SQL. There has to be a
way, right?
Why (#1): I'm working on a DDL-generating tool for DBAs who no-way-no-how
will let some Python tool change their database directly.
Why (#2): I can grab the SQL of most of the statements I want to run with
str() calls, like str(my_table.update().values(foo='bar')) and
str(sqlalchemy.schema.CreateTable(new_table)). But this doesn't generate
the same SQL that actually executing does; specifically, it doesn't create
the PK as SERIAL in postgres.
My code:
import sqlalchemy
connection_string = 'postgresql://user:pw@localhost/db'
engine = sqlalchemy.create_engine(connection_string, echo=True)
metadata = sqlalchemy.MetaData(bind=engine)
new_table = sqlalchemy.Table('new_table', metadata,
sqlalchemy.Column('id',
sqlalchemy.types.Integer,
primary_key = True,
autoincrement = True))
print(sqlalchemy.schema.CreateTable(new_table))
print('\n\n-\n\n')
metadata.create_all(engine)
Result:
CREATE TABLE new_table (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
-
2013-03-19 21:12:45,248 INFO sqlalchemy.engine.base.Engine
CREATE TABLE new_table (
id SERIAL NOT NULL,
PRIMARY KEY (id)
)
As far as I can tell, Alembic isn't quite right for this task; I'm writing
for users who won't want to set up an alembic directory with named
revisions and so forth. But something analogous to Alembic's offline
mode is exactly what I'm looking for for SQLAlchemy.
Thanks,
--
- Catherine
http://catherinedevlin.blogspot.com
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] reflected DATETIME
I'm trying to build a MySQL-PostgreSQL migration tool that reflects tables from MySQL, then creates them in pg. So far so good, except that when SQLAlchemy reflects a MySQL table with a DATETIME column, it reflects it as a sqlalchemy.types.DATETIME, then tries to create a DATETIME in PostgreSQL. I get LINE 7: SFDC_LAST_UPDATED DATETIME DEFAULT '-00-00 00:00:00' ... sqlalchemy.exc.ProgrammingError: (ProgrammingError) type datetime does not exist I haven't been able to come up with a way around this... either change the column's type after reflection to DateTime (how?), or command sqlalchemy to reflect them as sqlalchemy.DateTime in the first place (how?), or... I don't know. I can't hard-code the column definitions b/c I want the tool to adapt when the original (MySQL) database is changed. Can anybody help? Thanks very much! -- - Catherine http://catherinedevlin.blogspot.com -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] many-to-many doc clarification suggestion
When I went looking for docs to remind myself how to build a many-to-many
relationship, the first doc I found at
http://www.sqlalchemy.org/docs/orm/extensions/declarative.html
keywords = Table(
'keywords', Base.metadata,
Column('author_id', Integer, ForeignKey('authors.id')),
Column('keyword_id', Integer, ForeignKey('keywords.id'))
)
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
keywords = relationship(Keyword, secondary=keywords)
... confused me completely, because I couldn't tell whether ``keywords`` was
the association table? (Which seemed to be implied by
``secondary=keywords``). And ``keywords`` has a foreign key
(``keyword_id``) which points to ``keywords.id``, a column that... doesn't
exist? Trying to run it threw ``sqlalchemy.exc.NoReferencedColumnError:
Could not create ForeignKey 'keywords.id' on table 'keywords': table
'keywords' has no column named 'id'``
Anyway, I suggest that it would be much less confusing to just refer to the
ORM tutorial example, Building a Many To Many Relationship at
http://www.sqlalchemy.org/docs/orm/tutorial.html, which I think is much more
clear but which unfortunately only shows up at position #14 in the search
results of a many-to-many search.
Thanks!
--
- Catherine
http://catherinedevlin.blogspot.com
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: interpret (string) NULLs as empty strings, not None
On Thu, Aug 6, 2009 at 3:14 PM, Michael Bayermike...@zzzcomputing.com wrote:
When populating objects through the ORM, I'd like to interpret all
NULL values fetched from VARCHAR2 / NVARCHAR2 columns in the database
as empty strings ('') instead of `None`s.
use a TypeDecorator that creates that behavior.
Belated but huge thanks for this. This is a much simpler solution
than I had hoped for.
class NullCatchingString(types.TypeDecorator):
# NULL results are returned as empty strings.
impl = types.String
def process_result_value(self, value, engine):
if value:
return value
else:
return ''
To get a global effect,
perhaps in your imports make sure that your custom type overrides the
usage of String/Unicode in your Table definitions.
In this case, my tables are autoload=True, so I don't think overriding
String will help. However, I realized that I've got a limited number
of columns that need this behavior, and explicitly defining those
columns isn't too much of a pain after all.
tbl = Table('mytable', metadata,
Column('suchandsuch', NullCatchingString),
autoload=True, autoload_with=engine)
Thanks again!
--
- Catherine
http://catherinedevlin.blogspot.com/
*** PyOhio * July 25-26, 2009 * pyohio.org ***
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] interpret (string) NULLs as empty strings, not None
When populating objects through the ORM, I'd like to interpret all
NULL values fetched from VARCHAR2 / NVARCHAR2 columns in the database
as empty strings ('') instead of `None`s.
Is there any way to set that behavior globally? Failing that, how
would you recommend doing it?
(I'm using Oracle, which does not distinguish between empty strings
and NULLs; but, once my data is on the Python side, I do a zillion
string operations on it and don't want to bother checking for `None`s
every time I slice, check `in`, etc. When I send data back to Oracle,
any empty strings I send will be converted back to NULLs as Oracle
saves them, anyway.)
Thanks!
--
- Catherine
http://catherinedevlin.blogspot.com/
*** PyOhio * July 25-26, 2009 * pyohio.org ***
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: preview an update?
On Thu, Jun 18, 2009 at 4:57 PM, Michael Bayermike...@zzzcomputing.com wrote:
you can get most of this stuff from the session without any flush
occurring. at the object level are the new, dirty, and deleted
collections on Session. At the attribute level the
attributes.get_history() method will illustrate the full changes made
since the last flush on an individual object attribute. get_history()
should be in the API docs.
Thank you! get_history is just what I needed. However, I spent a
long time being confused by something that I think is a very obscure
bug.
If you make an update to a table that has a relation to a view, then
get_history on an attribute in the view, then the history is
forgotten. I'm attaching a file that duplicates it completely, but
here's the gist of it (after setting up as described in the docs' ORM
tutorial):
ed = session.query(User).first()
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
((), [u'Ed Jones'], ())
ed.fullname = 'Eduardo Jones'
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
(['Eduardo Jones'], (), [u'Ed Jones'])
# so far so good
print sqlalchemy.orm.attributes.get_history(ed.userview, 'name')
((), [u'ed'], ())
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
((), ['Eduardo Jones'], ())
... now get_history thinks fullname has always been Eduardo Jones.
Is this a bug I should file, or something I should have expected?
Thanks as always!
--
- Catherine
http://catherinedevlin.blogspot.com/
*** PyOhio * July 25-26, 2009 * pyohio.org ***
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(12)),
Column('fullname', String(40)),
Column('password', String(40))
)
metadata.create_all(engine)
class User(object):
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return User('%s','%s', '%s') % (self.name, self.fullname, self.password)
from sqlalchemy.orm import mapper, relation, backref
mapper(User, users_table)
ed_user = User('ed', 'Ed Jones', 'edspassword')
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
session.add(ed_user)
session.commit()
#
# everything up to here comes from the ORM tutorial at http://www.sqlalchemy.org/docs/05/ormtutorial.html
engine.connect().execute('CREATE VIEW userview AS SELECT id, name FROM users')
class UserView(object):
pass
userview = Table('userview', metadata,
Column('id', ForeignKey('users.id'), primary_key=True),
autoload=True, autoload_with=engine)
mapper(UserView, userview,
properties={'user': relation(User, uselist=False, lazy=True,
backref=backref('userview', uselist=False))})
import sqlalchemy.orm.attributes
ed = session.query(User).first()
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
ed.fullname = 'Eduardo Jones'
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
print sqlalchemy.orm.attributes.get_history(ed.userview, 'name')
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
[sqlalchemy] preview an update?
I'm building a complex ETL tool with SQLAlchemy that will sometimes need to let the user preview a changed record - not actually carry out the update, just find out which fields would be changed, and to what values. I'm having trouble figuring out a good way to do it. Is there a way to get a sort of preview of an update - get information about what updates are pending for the next flush, or get a flush to report back its changes without actually performing it? One approach would be to set echo to True, let the user see the echo, then roll back the transaction, but there are a couple reasons I don't think that will work for me. I don't want to make the user mentally parse SQL statements; I'd like to get a dict of the pending changes and write my own code to display them attractively, instead. Anyway, I'm having trouble getting echo to work on this app. (The whole thing is part of a TurboGears project, and my echoes aren't showing up, probably because of something I don't understand about the SQLAlchemy logging configuration.) Just getting to the bind variables that will ultimately be sent along with the UPDATE statement would be a great solution, and I'm trying to figure out where I could get them. Right now, it looks like the `params` dict is assembled in Mapper._save_obj in orm/mapper.py, used at line 1376 to issue connection.execute, then discarded. I'm considering overriding Mapper with my own version whose __save_obj can exit at this point, returning `params`, if it is invoked with a preview=True parameter... but that seems a little scary. __save_obj is a long method, and I'd have to keep my app's version of it carefully synched with the canonical sqlalchemy version indefinitely. Thanks in advance for reading through, and for any any suggestions! -- - Catherine http://catherinedevlin.blogspot.com/ *** PyOhio * July 25-26, 2009 * pyohio.org *** --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
