[sqlalchemy] Brewery: Heterogenous data streams with SQL Alchemy
Hi, I am working on a framework called Brewery. Goal is to provide abstract interface for data streams from heterogenous sources into heterogenous targets. More information with images: http://databrewery.org/doc/streams.html Point is to have objects similar to file streams, but streaming structured data in form of records/rows instead of bytes. STREAMS Currently implemented sources/targets are: * Relational database table through SQLAlchemy (source+target) * CSV file (source+target) * XLS file (source only) * MongoDB (source+target) * google spreadsheet (source only) * directory with YAML files - one file per record (source+target) For each source there are three basic methods: - fields - list of fields provided by the source (has to be explicitly set for sources with unknown fields) - rows() - iterator for data represented by list - records() - iterator for data represented by dict object Optionally you can use: read_fields(limit) to learn what fields are present in data source (for example in mongo DB) For each target: - append() - append an object, either a dictionary or a list to the target With this simple interface you can easily create pipes between MongoDB and Postgres, import directory of YAML files into MySQL, ... DATA QUALITY In addition to that, there is simple data auditing tool for basic data quality audit. You can use StreamAuditor (stream target) to collect information about data and then generate data quality report. Currently audited data properties are: * record and value count (might be different in document based DBs,same in relational) * null count * empty string count * distinct value count * distinct values * storage types (only one for relational databases) * ratios of measured properties, such as null/value count or null/ record count More probes to come (in a modular way). API is documented here: http://databrewery.org/doc/api/index.html Sources: bitbucket: https://bitbucket.org/Stiivi/brewery (main - mercurial repository) github: https://github.com/Stiivi/brewery/ (synchronized with main) Example usage: Some source streams (XLS/CSV) are already being used for data proxy in project CKAN for converting data from various resources into common structured form: http://blog.ckan.org/2011/01/11/raw-data-in-ckan-resources-and-data-proxy/ FUTURE Plans for the future are: * command-line tools for simple data streaming tasks: copy, quality audit * data processing stream network with nodes for simple transformations, analysis and data mining * modular data quality probes - injectable into the network The Brewery project is in early stage. I would like have some feedback: what do you think about it? Do you have any suggestions, comments? If anyone would like to try it and will have any troubles, just drop me a line and I will help. Regards, Stefan Urbanek -- Twitter: @Stiivi -- 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] Query doesn't work for Sybase?
Hi All I am using sqlalchemy 0.6.5 with sybase dialect. Python-Sybase driver is 0.39. The query SQL generated by sqlachemy has double quotes on each identifier, something like: SELECT MYTABLE.ID AS MYTABLE_ID FROM MYTABLE among which MYTABLE.ID is syntactically wrong in ASE 12.5. Is this a known issue or I have done something wrong? Can I turn off the quotes on the column identifiers? Thanks Joel -- 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: rollback not working
I am not an expert in these concepts, so just trying to make sure I understand what you said. 1. If I use connection.execute(), then then every sql statements is not put in its own transactions. 2. But If I use connection-less execution like table.execute or engine.execute() then every statement is put in its own transaction. Can you confirm if the above understanding is correct. Also what is the DBAPI that you generally refer to ? -- 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.
Re: [sqlalchemy] Query doesn't work for Sybase?
use lower case names for your column names in Table metadata, so that they are case insensitive. they will not be quoted. On Jan 21, 2011, at 2:00 AM, Joel Zhou wrote: Hi All I am using sqlalchemy 0.6.5 with sybase dialect. Python-Sybase driver is 0.39. The query SQL generated by sqlachemy has double quotes on each identifier, something like: SELECT MYTABLE.ID AS MYTABLE_ID FROM MYTABLE among which MYTABLE.ID is syntactically wrong in ASE 12.5. Is this a known issue or I have done something wrong? Can I turn off the quotes on the column identifiers? Thanks Joel -- 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. -- 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.
Re: [sqlalchemy] Brewery: Heterogenous data streams with SQL Alchemy
seems like a nice effort, generic ETLs are tough! On Jan 20, 2011, at 10:25 PM, Stefan Urbanek wrote: Hi, I am working on a framework called Brewery. Goal is to provide abstract interface for data streams from heterogenous sources into heterogenous targets. More information with images: http://databrewery.org/doc/streams.html Point is to have objects similar to file streams, but streaming structured data in form of records/rows instead of bytes. STREAMS Currently implemented sources/targets are: * Relational database table through SQLAlchemy (source+target) * CSV file (source+target) * XLS file (source only) * MongoDB (source+target) * google spreadsheet (source only) * directory with YAML files - one file per record (source+target) For each source there are three basic methods: - fields - list of fields provided by the source (has to be explicitly set for sources with unknown fields) - rows() - iterator for data represented by list - records() - iterator for data represented by dict object Optionally you can use: read_fields(limit) to learn what fields are present in data source (for example in mongo DB) For each target: - append() - append an object, either a dictionary or a list to the target With this simple interface you can easily create pipes between MongoDB and Postgres, import directory of YAML files into MySQL, ... DATA QUALITY In addition to that, there is simple data auditing tool for basic data quality audit. You can use StreamAuditor (stream target) to collect information about data and then generate data quality report. Currently audited data properties are: * record and value count (might be different in document based DBs,same in relational) * null count * empty string count * distinct value count * distinct values * storage types (only one for relational databases) * ratios of measured properties, such as null/value count or null/ record count More probes to come (in a modular way). API is documented here: http://databrewery.org/doc/api/index.html Sources: bitbucket: https://bitbucket.org/Stiivi/brewery (main - mercurial repository) github: https://github.com/Stiivi/brewery/ (synchronized with main) Example usage: Some source streams (XLS/CSV) are already being used for data proxy in project CKAN for converting data from various resources into common structured form: http://blog.ckan.org/2011/01/11/raw-data-in-ckan-resources-and-data-proxy/ FUTURE Plans for the future are: * command-line tools for simple data streaming tasks: copy, quality audit * data processing stream network with nodes for simple transformations, analysis and data mining * modular data quality probes - injectable into the network The Brewery project is in early stage. I would like have some feedback: what do you think about it? Do you have any suggestions, comments? If anyone would like to try it and will have any troubles, just drop me a line and I will help. Regards, Stefan Urbanek -- Twitter: @Stiivi -- 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. -- 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: SQLAlchemy case insensitive like for unicode word
Thanks for response!
On 21 янв, 00:29, Michael Bayer mike...@zzzcomputing.com wrote:
That's SQLite's lower() function. If you'd like to use Python's lower()
function, you should call lower() on the string and use
column.like(mystring.lower()). But that won't do case-insensitive comparison
since you need to call lower() on the database column in the statement.
So you really need to first get this statement to work with pysqlite, to
figure out what collation issues may exist with sqlite3 or encoding issues
with pysqlite:
import sqlite3
connection = sqlite3.connect(/path/to/your/db)
cursor = connection.cursor()
cursor.execute(SELECT * FROM patients WHERE lower(lastname) LIKE lower(?),
['Ivanov'])
print cursor.fetchall()
SQLAlchemy doesn't look at the contents of your string at all with pysqlite,
its a pass through.
On Jan 20, 2011, at 4:15 PM, proft wrote:
Hello!
I have gtk application with sqlite db, contain russian words.
My model
code
class Patient(Base):
lastname = Column(Unicode)
/code
Search operation
code
patients = self.session.query(Patient)
lastname = unicode(self.lastname_entry.get_text())
if lastname:
patients = patients.filter(Patient.lastname.like(u%%%s%% %
lastname))
/code
It perfectly work if i search as 'Ivanov', but didn't return results
if i search 'ivanov'. Note, i search russian words, not english.
In SQLite manager (firefox plugin) search query
code
SELECT * FROM patients WHERE lastname LIKE %ivanov%
/code
If i query db and look at value:
code
In [28]: p.lastname
Out[28]: u'\u0413\u0430\u043f\u0447\u0443\u043a'
/code
and than check it in query, generated by SQLAlchemy
code
In [29]: patients.filter(Patient.lastname.ilike(u%%%s%% %
l.decode('utf-8'))).count()
2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250
SELECT count(1) AS count_1
FROM patients
WHERE lower(patients.lastname) LIKE lower(?)
2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250
(u'%\u0433\u0430\u043f\u0447\u0443\u043a%',)
Out[29]: 0
/code
It is looked like lower function in SQLAlchemy didn't understood
cyrillic ...
Thanks!
PS: Sorry for my english)
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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.
Re: [sqlalchemy] Re: rollback not working
On Jan 21, 2011, at 9:16 AM, bool wrote: I am not an expert in these concepts, so just trying to make sure I understand what you said. 1. If I use connection.execute(), then then every sql statements is not put in its own transactions. 2. But If I use connection-less execution like table.execute or engine.execute() then every statement is put in its own transaction. so the relevant info we're talking about is here: http://www.sqlalchemy.org/docs/core/connections.html connectionless execution - execute statement in a new transaction, if its an INSERT/UPDATE/DELETE, autocommit: engine.execute(select * from table) implicit, connectionless execution - same behavior, executes in a new transaction, autocommits: table.insert().execute(a=5, b=4) explicit execution - uses a Connection, but will autocommit conn = engine.connect() conn.execute(insert into table (a, b, c) values (1, 2, 3)) ...unless you start a transaction: trans = conn.begin() conn.execute(insert into table (a, b, c) values (1, 2, 3)) conn.execute(insert into table (a, b, c) values (4, 5, 6)) trans.commit() ORM: using the Session, you're in a transaction for all operations, bounded by rollback()/commit(): sess = Session() sess.execute(insert into table (a, b, c) values (1, 2, 3)) sess.execute(insert into table (a, b, c) values (4, 5, 6)) sess.commit() ... unless you're using autocommit : sess = Session(autocommit=True) Can you confirm if the above understanding is correct. Also what is the DBAPI that you generally refer to ? DBAPI is what you're talking to your database with: http://www.python.org/dev/peps/pep-0249/ -- 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] Inserting in a container class that behaves as list (or set) from the contained class (not from the container)
Hello list!
I have a couple of classes. One of the behaves as the container of the other:
class ContainerOfSamples(declarativeBase):
__tablename__ = containers
_id = Column(id, Integer, primary_key=True)
_samples = relationship(Samples, cascade=all, delete,
collection_class=set)
def setSamples(self, samples):
self._samples = samples
def getSamples(self):
return self._samples
def addSample(self, sample):
self._samples.add(sample)
id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
samples = sqlalchemy.orm.synonym('_samples',
descriptor=property(getSamples,
setSamples))
class Sample(declarativeBase):
__tablename__ = containers
_id = Column(id, Integer, primary_key=True)
_whatever = Column(whatever, String(20))
_containerId = Column(container_id, Integer,
ForeignKey(containers.id))
_container = relationship(Container, uselist=False)
def __hash__(self):
return int(self.id)
def setContainer(self, container):
self._container = container
def getContainer(self):
return self._container
id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
whatever = sqlalchemy.orm.synonym('_whatever',
descriptor=property(getWhatever,
setWhatever))
container = sqlalchemy.orm.synonym('_container',
descriptor=property(getContainer,
setContainer))
It's a relationship 1:N (one sample can be in 1 container, 1 container
can have N samples)... basically, a list...
If I have an instance of ContainerOfSamples and I want to add a
sample, I can do:
container = ContainerOfSamples()
sample = Sample()
container.addSample(sample)
And the sample is properly added, the relationships are all
initialized/created/set (however you want to call it) properly... the
containerId in the sample is the id of the container instance...
perfect.
So now the question is: Is there a way of getting the same effect from
the Sample class? Something like:
sample = Sample()
container = ContainerOfSamples()
sample.container(container)
And then in the container instance the Sample sample would be
added to the container.samples set?
It doesn't seem to work... for some reason, if I try to do that, the
sample._containerId becames the id of the sample...
I don't know if playing with the backref would give me what I
want... I've made a few tries, but it doesn't seem to improve... Maybe
I have a misconception here :-(
Any hints, examples, link to examples... would be helpful and deeply
appreciated. Thank you in advance!
--
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.
Re: [sqlalchemy] Inserting in a container class that behaves as list (or set) from the contained class (not from the container)
On Jan 21, 2011, at 12:29 PM, Hector Blanco wrote:
Hello list!
I have a couple of classes. One of the behaves as the container of the other:
class ContainerOfSamples(declarativeBase):
__tablename__ = containers
_id = Column(id, Integer, primary_key=True)
_samples = relationship(Samples, cascade=all, delete,
collection_class=set)
def setSamples(self, samples):
self._samples = samples
def getSamples(self):
return self._samples
def addSample(self, sample):
self._samples.add(sample)
id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
samples = sqlalchemy.orm.synonym('_samples',
descriptor=property(getSamples,
setSamples))
class Sample(declarativeBase):
__tablename__ = containers
_id = Column(id, Integer, primary_key=True)
_whatever = Column(whatever, String(20))
_containerId = Column(container_id, Integer,
ForeignKey(containers.id))
_container = relationship(Container, uselist=False)
def __hash__(self):
return int(self.id)
def setContainer(self, container):
self._container = container
def getContainer(self):
return self._container
id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
whatever = sqlalchemy.orm.synonym('_whatever',
descriptor=property(getWhatever,
setWhatever))
container = sqlalchemy.orm.synonym('_container',
descriptor=property(getContainer,
setContainer))
sample = Sample()
container = ContainerOfSamples()
sample.container(container)
I don't understand the need for the synonyms, but shouldn't this be as simple
as sample.container = container? The relationship on sample is already
defined... maybe you are confused because you think you need these getters and
setters- in the above example, I don't see any need for them.
Cheers,
M
--
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] On creating new SchemaItems
Hello,
I would like SQLAlchemy to generate views much in the same way it can generate
tables- perhaps like this:
View('bob',select([...]))
Is the SQLAlchemy code modular enough to support a user-defined SchemaItem or
does that require changes to SQLAlchemy itself?
The reason I would very much like this is because I currently use the Table
objects, munge them through a processor to add common attributes, and generate
a schema- I would like to be able to do the same with View objects.
I looked at subclassing sqlalchemy.schema.Table, but the __new__ override and
the fact that the sql.compiler.DDLCompiler has hardcoded
visit_create_schemaitem names gives me pause as to whether or not this can be
accomplished without modifying SQLAlchemy itself.
I realize that questions surrounding view pop up from time-to-time, so does it
make sense to create or support a dialect-specific or user-defined SchemaItem?
Thanks!
Cheers,
M
--
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.
Re: [sqlalchemy] Query doesn't work for Sybase?
Thanks for your reply. I have found an option in creating table to force the quoting off. Just pass quote=false as additional argument when creating tables, it works. Thank you all the same. Joel 发自我的 iPhone 在 2011-1-21，23:24，Michael Bayer mike...@zzzcomputing.com 写道： use lower case names for your column names in Table metadata, so that they are case insensitive. they will not be quoted. On Jan 21, 2011, at 2:00 AM, Joel Zhou wrote: Hi All I am using sqlalchemy 0.6.5 with sybase dialect. Python-Sybase driver is 0.39. The query SQL generated by sqlachemy has double quotes on each identifier, something like: SELECT MYTABLE.ID AS MYTABLE_ID FROM MYTABLE among which MYTABLE.ID is syntactically wrong in ASE 12.5. Is this a known issue or I have done something wrong? Can I turn off the quotes on the column identifiers? Thanks Joel -- 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. -- 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. -- 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.
Re: [sqlalchemy] Inserting in a container class that behaves as list (or set) from the contained class (not from the container)
Thank you for the quick reply.
shouldn't this be as simple as sample.container = container
Yeah... I thought so too... And actually, the getter/setters (the
synonym or property) just do that... (and a check for the parameter
type):
class Sample(declarativeBase):
# yadda, yadda, yadda ...
def setContainer(self, container):
if isinstance(container, Container):
self._container = container
else:
raise TypeError(received a %s when expecting a
Container % type(container))
Anyway... if my idea is not wrong, I'll check if the error is
somewhere else. It's good to know that I'm going in the right
direction!
Thank you!
2011/1/21 A.M. age...@themactionfaction.com:
On Jan 21, 2011, at 12:29 PM, Hector Blanco wrote:
Hello list!
I have a couple of classes. One of the behaves as the container of the other:
class ContainerOfSamples(declarativeBase):
__tablename__ = containers
_id = Column(id, Integer, primary_key=True)
_samples = relationship(Samples, cascade=all, delete,
collection_class=set)
def setSamples(self, samples):
self._samples = samples
def getSamples(self):
return self._samples
def addSample(self, sample):
self._samples.add(sample)
id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
samples = sqlalchemy.orm.synonym('_samples',
descriptor=property(getSamples,
setSamples))
class Sample(declarativeBase):
__tablename__ = containers
_id = Column(id, Integer, primary_key=True)
_whatever = Column(whatever, String(20))
_containerId = Column(container_id, Integer,
ForeignKey(containers.id))
_container = relationship(Container, uselist=False)
def __hash__(self):
return int(self.id)
def setContainer(self, container):
self._container = container
def getContainer(self):
return self._container
id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
whatever = sqlalchemy.orm.synonym('_whatever',
descriptor=property(getWhatever,
setWhatever))
container = sqlalchemy.orm.synonym('_container',
descriptor=property(getContainer,
setContainer))
sample = Sample()
container = ContainerOfSamples()
sample.container(container)
I don't understand the need for the synonyms, but shouldn't this be as simple
as sample.container = container? The relationship on sample is already
defined... maybe you are confused because you think you need these getters
and setters- in the above example, I don't see any need for them.
Cheers,
M
--
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.
--
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.
Re: [sqlalchemy] On creating new SchemaItems
On Jan 21, 2011, at 12:56 PM, A.M. wrote:
Hello,
I would like SQLAlchemy to generate views much in the same way it can
generate tables- perhaps like this:
View('bob',select([...]))
Is the SQLAlchemy code modular enough to support a user-defined SchemaItem or
does that require changes to SQLAlchemy itself?
The reason I would very much like this is because I currently use the Table
objects, munge them through a processor to add common attributes, and
generate a schema- I would like to be able to do the same with View objects.
I looked at subclassing sqlalchemy.schema.Table, but the __new__ override and
the fact that the sql.compiler.DDLCompiler has hardcoded
visit_create_schemaitem names gives me pause as to whether or not this can
be accomplished without modifying SQLAlchemy itself.
I realize that questions surrounding view pop up from time-to-time, so does
it make sense to create or support a dialect-specific or user-defined
SchemaItem?
Thanks!
You may not be aware that we have a full API for creation of custom SQL
expression subclasses as well as establishing compilation rules, which is
documented at http://www.sqlalchemy.org/docs/core/compiler.html .
Regarding views specifically, we've got a usage recipe against this system,
though I don't know if its seen any real world usage, at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views . Its using lower-case
table() objects as the core structure, which is your basic thing with a bunch
of columns object, the superclass of Table that doesn't have the hard linkages
with MetaData or constraints, so no SchemaItem subclass is needed. Subclassing
TableClause (the result of table()) would be the likely way to go if you wanted
your view construct to have extra features.
Cheers,
M
--
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.
--
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.
Re: [sqlalchemy] On creating new SchemaItems
On Jan 21, 2011, at 1:36 PM, Michael Bayer wrote:
On Jan 21, 2011, at 12:56 PM, A.M. wrote:
Hello,
I would like SQLAlchemy to generate views much in the same way it can
generate tables- perhaps like this:
View('bob',select([...]))
Is the SQLAlchemy code modular enough to support a user-defined SchemaItem
or does that require changes to SQLAlchemy itself?
The reason I would very much like this is because I currently use the Table
objects, munge them through a processor to add common attributes, and
generate a schema- I would like to be able to do the same with View objects.
I looked at subclassing sqlalchemy.schema.Table, but the __new__ override
and the fact that the sql.compiler.DDLCompiler has hardcoded
visit_create_schemaitem names gives me pause as to whether or not this can
be accomplished without modifying SQLAlchemy itself.
I realize that questions surrounding view pop up from time-to-time, so does
it make sense to create or support a dialect-specific or user-defined
SchemaItem?
Thanks!
You may not be aware that we have a full API for creation of custom SQL
expression subclasses as well as establishing compilation rules, which is
documented at http://www.sqlalchemy.org/docs/core/compiler.html .
Regarding views specifically, we've got a usage recipe against this system,
though I don't know if its seen any real world usage, at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views . Its using
lower-case table() objects as the core structure, which is your basic thing
with a bunch of columns object, the superclass of Table that doesn't have
the hard linkages with MetaData or constraints, so no SchemaItem subclass is
needed. Subclassing TableClause (the result of table()) would be the likely
way to go if you wanted your view construct to have extra features.
I guess I am curious as to why there should be a built-in way to compile
SchemaItems and then a user way to do the same thing. Is there a plan to
unify these methods?
As a python programmer, it seems more natural to me to subclass the relevant
class than to spam my class with decorators. Does it make sense to offer
user-defined SchemaItems which would play well with metadata much like there
are user-defined types?
Cheers,
M
--
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.
Re: [sqlalchemy] Re: I need a final push
Hello Michael, Thanks for your explanation. Uhmpf Ok, the book is trashed and burnt. Oh no not all I wrote came out of the book I used it as a guide to setup a SQLAlchemy without Elixir. The part using Python as a SQLite plugin came form http://docs.python.org/py3k/library/sqlite3.html I think I will probably need some time to implement it all. I will let you know when finished. Thanks again. Frans. -- 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.
Re: [sqlalchemy] On creating new SchemaItems
On Jan 21, 2011, at 3:15 PM, A.M. wrote: I guess I am curious as to why there should be a built-in way to compile SchemaItems and then a user way to do the same thing. Is there a plan to unify these methods? I've considered it but haven't drawn a picture of what that would really look like.The decorator approach seems appropriate for ad-hoc constructs that aren't part of the core group of constructs, whereas the core hierarchy of Compiler classes feels more appropriate for supplying the kind of variability we see in different database SQL implementations. Perhaps what's at play here is that Compiler subclasses handle variability in DBAPI/SQL interaction, whereas decorators handle variability in sets of supported constructs. But this would all be better addressed if someone wanted to propose what the unified system would look like, including specifics to both the base Compiler and SQLCompiler classes, as well as a few dialects like the MySQL and PG dialects. I definitely do not want user-defined structures requiring the construction of a separate Compiler or CompilerExtension class, however, this is too cumbersome and is the opposite of the direction we are headed in SQLA 0.7. So a system whereby all of the dozen or so dialect implementations no longer have Compiler subclasses, the Compiler and SQLCompiler classes themselves no longer define SQL strings and likely become final (i.e. not usually subclassed), would be what it looks like. It would involve rewriting about 50% of each dialect. Ultimately both systems use the same dispatch function, though it is optimized in the case of builtins to have fewer method calls (which is also critical). As a python programmer, it seems more natural to me to subclass the relevant class than to spam my class with decorators. Does it make sense to offer user-defined SchemaItems which would play well with metadata much like there are user-defined types? Not sure where you're getting the notion of spamming a class with decorators - which class specifically would have decorators ? The compiler system requires the construction of the ClauseElement subclass separate from the functions that define its SQL compilation, which have no class-bound requirement. Also SchemaItem is actually a separate inheritance chain than that of ClauseElement. SchemaItem subclasses don't have SQL representations, such as a ForeignKeyConstraint. Its the CreateConstraint and DropConstraint elements, ultimately descending from ClauseElement, that define a SQL representation. As far as MetaData, we offer event-based interaction such that create and drop events can be intercepted. The other purpose of MetaData is to serve as a registry of tables so that they are addressable by ForeignKey objects. If further levels of registry behavior are proposed along with their use cases we can consider them for inclusion. -- 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.
