[sqlalchemy] Re: Returning single col, multiple rows in a single row
Hi Michael, Thanks for the reply, and apologies to everyone...I didn't actually realize SQLAlchemy was a DB toolkit itself, I thought it was just a group for SQL wizards!! I found a solution using GROUP_CONCAT over in a MySQL group. Regards, Russ. --~--~-~--~~~---~--~~ 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: database definitions - was sqlalchemy migration/schema creation
On Thu, 8 May 2008 19:05:20 -0500 Lukasz Szybalski [EMAIL PROTECTED] wrote: Do you guys know what would give me column definition of table? There is an autocode tool that, although with some glitches on it own, does exactly what you are looking for. See http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode (but note that the current version is not the one attached to the page, but rather lives in a repository on code.google.com). ciao, lele. -- nickname: Lele Gaifax| Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas| comincerò ad aver paura di chi mi copia. [EMAIL PROTECTED] | -- Fortunato Depero, 1929. --~--~-~--~~~---~--~~ 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: database definitions - was sqlalchemy migration/schema creation
On Fri, May 9, 2008 at 4:46 AM, [EMAIL PROTECTED] wrote:
On Friday 09 May 2008 03:05, Lukasz Szybalski wrote:
Do you guys know what would give me column definition of table?
do u want it as generated source-text or what?
Yes. The Final output I would like is the txt version of db definitions.
autoload --- sqlalchemy.Column('Address_Sid', sqlalchemy.Integer,
primary_key=True),
have a look at dbcook/dbcook/misc/metadata/autoload.py
at dbcook.sf.net
I have a table that I autoload and I would like to get this:
address_table = sqlalchemy.Table('address', metadata,
sqlalchemy.Column('Address_Sid', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('FirstName', sqlalchemy.Unicode(40),nullable=False),
sqlalchemy.Column('LastName', sqlalchemy.Unicode(40),nullable=False),
sqlalchemy.Column('MaidenLastName', sqlalchemy.Unicode(40)),
sqlalchemy.Column('Email', sqlalchemy.Unicode(80),nullable=False),
So:
I can get a list of tables:
for i in metadata.tables:
... print i
list of columns:
for i in metadata.tables['WMI'].original_columns:
... print i
How do I get column type, indexes and primary keys?
Lucas
On Tue, Apr 15, 2008 at 7:44 PM, Lukasz Szybalski [EMAIL PROTECTED]
wrote:
Hello,
Is there maybe a feature in sqlalchemy that would allow me to autoload
table from one database, and move it over to another database?
1. I would like to print data structure from autoload table. (then
copy and paste it into new file and use it to create new
table)(without typing every data structure)
2. And/or autoload via one engine and autoupload via different engine
and create_all()
3. Analyze csv files and create sqlalchemy definition like structure.
Ideas?
Lucas
--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf
--~--~-~--~~~---~--~~
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: Result Set From An Oracle Function
On May 9, 2008, at 4:26 AM, Dan wrote:
Using cx_Oracle, the following does the trick (note this is a
contrived example):
def test(orcl_conn):
curs = orcl_conn.cursor()
cursorToBind = orcl_conn.cursor()
curs.execute(begin
:cr1 := aaa_test(pWhen = :arg1);
end;, arg1 = None, cr1 = cursorToBind)
marketData = {}# dictionary to hold the
records
i = 1
for col in cursorToBind.fetchall():
marketData[str(i)] = col
i = i + 1
return marketData
why is it necessary to send the cursor in as a bind parameter ? I've
used custom PL/SQL plenty and i've never heard of that technique.
--~--~-~--~~~---~--~~
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: database definitions - was sqlalchemy migration/schema creation
On Friday 09 May 2008 16:32:25 Lukasz Szybalski wrote:
On Fri, May 9, 2008 at 4:46 AM, [EMAIL PROTECTED] wrote:
On Friday 09 May 2008 03:05, Lukasz Szybalski wrote:
Do you guys know what would give me column definition of table?
do u want it as generated source-text or what?
Yes. The Final output I would like is the txt version of db
definitions.
autoload --- sqlalchemy.Column('Address_Sid', sqlalchemy.Integer,
primary_key=True),
well, then see that one.
have a look at dbcook/dbcook/misc/metadata/autoload.py
at dbcook.sf.net
running 'python autoload.py dburl' will dump the db-metadata into
src-text, trying to convert some types into their generic form.
see also other files in that directory, copyall.py and copydata.py,
for copying stuff between dburls.
ciao
svil
I have a table that I autoload and I would like to get this:
address_table = sqlalchemy.Table('address', metadata,
sqlalchemy.Column('Address_Sid', sqlalchemy.Integer,
primary_key=True), sqlalchemy.Column('FirstName',
sqlalchemy.Unicode(40),nullable=False),
sqlalchemy.Column('LastName',
sqlalchemy.Unicode(40),nullable=False),
sqlalchemy.Column('MaidenLastName', sqlalchemy.Unicode(40)),
sqlalchemy.Column('Email',
sqlalchemy.Unicode(80),nullable=False),
So:
I can get a list of tables:
for i in metadata.tables:
... print i
list of columns:
for i in metadata.tables['WMI'].original_columns:
... print i
How do I get column type, indexes and primary keys?
Lucas
On Tue, Apr 15, 2008 at 7:44 PM, Lukasz Szybalski
[EMAIL PROTECTED]
wrote:
Hello,
Is there maybe a feature in sqlalchemy that would allow me to
autoload table from one database, and move it over to another
database?
1. I would like to print data structure from autoload table.
(then copy and paste it into new file and use it to create
new table)(without typing every data structure)
2. And/or autoload via one engine and autoupload via different
engine and create_all()
3. Analyze csv files and create sqlalchemy definition like
structure.
Ideas?
Lucas
--~--~-~--~~~---~--~~
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: Result Set From An Oracle Function
Not sure how to do it otherwise. This is how its been coded -- what
is the alternative?
On May 9, 8:34 am, Michael Bayer [EMAIL PROTECTED] wrote:
On May 9, 2008, at 4:26 AM, Dan wrote:
Using cx_Oracle, the following does the trick (note this is a
contrived example):
def test(orcl_conn):
curs = orcl_conn.cursor()
cursorToBind = orcl_conn.cursor()
curs.execute(begin
:cr1 := aaa_test(pWhen = :arg1);
end;, arg1 = None, cr1 = cursorToBind)
marketData = {}# dictionary to hold the
records
i = 1
for col in cursorToBind.fetchall():
marketData[str(i)] = col
i = i + 1
return marketData
why is it necessary to send the cursor in as a bind parameter ? I've
used custom PL/SQL plenty and i've never heard of that technique.
--~--~-~--~~~---~--~~
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] The IN Construct
Hello, I would like to use the IN construct in one of my queries: i.e. WHERE c.id IN (..subquery here) But I can't find any sqlalchemy support for It. It's hvery hard to search for :( Anyone know? Thanks --~--~-~--~~~---~--~~ 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: The IN Construct
in_ is a method that exists on a column. You pass it the list of things that the column value should be in. For example, if you have a class called MyClass that is mapped to a table and has a column called id, you can do: session.query(MyClass).filter(MyClass.id.in_( [ 3, 4] )).all() On Fri, May 9, 2008 at 10:35 AM, Googli S [EMAIL PROTECTED] wrote: Hello, I would like to use the IN construct in one of my queries: i.e. WHERE c.id IN (..subquery here) But I can't find any sqlalchemy support for It. It's hvery hard to search for :( Anyone know? Thanks --~--~-~--~~~---~--~~ 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: Problem with non-null fields
TP wrote: Hi, I have a model with a field called 'name' that is set to be non- null. When I look at the actual table created in MySQL the field really does say it cannot be null. However, when I try to set it to None and commit() the changes, I get a warning printed /Users/tp/sw/python-extensions/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/databases/mysql.py:1488: Warning: Column 'name' cannot be null But the underlying database IS changed with the field being set to the empty string ''. This seems wrong doesn't it? Am I doing something wrong? I'm using SQLAlchemy 0.4.5 on Mac OS X with MySQL 5.1. Thanks for any help! It's a server configuration issue- the server is using a legacy compatibility mode. Check the mysql docs for configuring sql mode to a modern, strict setting. --~--~-~--~~~---~--~~ 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] Insert-or-update pattern
Here's a pattern that I see a lot that SQLAlchemy doesn't really seem to support: update a row if it exists, or insert it if it doesn't. Imagine you're writing a web spider, so you've got a table with a primary key of the URL, with another column for the page's body. Your spider comes across a page which it might or might not have seen before. What it should do is try to insert the page, and if that fails with a duplicate key error, then try to update it. There is no way to do this in one line in SQLAlchemy, but there should be. 1. save_or_update does not work, because it does not try the insert and catch a failure; it merely checks to see if the object in the session is marked as being already persisted. When it does try the insert, if the object with that primary key is already there, it fails. 2. You could do it the long way: try: page = Page (url = url, body = body) Session.save(page) Session.commit() except IntegrityError: Session.close() page = Session.query(Page).filter(url = url)... page.body = body Session.save(page) Session.commit() But boy is that a lot of lines! What I want to do is: page = Page (url = url, body = body) Session.insert_or_update(page) Session.commit() Is this functionality there, and I just don't understand it? --~--~-~--~~~---~--~~ 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: Result Set From An Oracle Function
On May 9, 2008, at 10:20 AM, Dan wrote:
Not sure how to do it otherwise. This is how its been coded -- what
is the alternative?
what happens if you just say, cursor.execute(select * from
aaa_test(pWhen=:arg1, {'arg1':None}) using raw cx_oracle (and then
cursor.fetchall()) ? doesn't work ?
it almost looks like you're using an OUT param, which we *do* support,
that looks like this:
result = engine.execute(text(begin foo(:x, :y, :z); end;,
bindparams=[bindparam('x', Numeric), outparam('y', Numeric),
outparam('z', Numeric)]), x=5)
print result.out_parameters
otherwise we'd have to build another construct like x =
bound_cursor() to support this inline with SQLA.
you do of course have the option to just keep it as a cx_oracle thing
- just grab a cursor off of a Connection:
conn = engine.connect()
cursor = conn.connection.cursor()
then do the cx_oracle specific work with cursor.
--~--~-~--~~~---~--~~
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: Insert-or-update pattern
On May 9, 2008, at 12:02 PM, David Turner wrote: page = Page (url = url, body = body) Session.insert_or_update(page) Session.commit() Is this functionality there, and I just don't understand it? here is the usual way: page = Session.query(Page).filter(Page.url == url, Page.body==body).first() if not page: page = Page(url = url, body=body) session.save(page) If you know the primary key identifiers of the object, you may also use merge(): page = Page(primary key identifiers) persisted_page = Session.merge(page) the latter will do the same thing automatically as the former (SQLA does not use IntegrityErrors to query the 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: Problem with non-null fields
Who knew that marking a field as non-null didn't really make it non- null? Apparently you have to add the following to your my.cnf to tell MySQL your're actually serious about enforcing things: sql-mode='STRICT_TRANS_TABLES' On May 9, 11:30 am, jason kirtland [EMAIL PROTECTED] wrote: TP wrote: Hi, I have a model with a field called 'name' that is set to be non- null. When I look at the actual table created in MySQL the field really does say it cannot be null. However, when I try to set it to None and commit() the changes, I get a warning printed /Users/tp/sw/python-extensions/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/databases/mysql.py:1488: Warning: Column 'name' cannot be null But the underlying database IS changed with the field being set to the empty string ''. This seems wrong doesn't it? Am I doing something wrong? I'm using SQLAlchemy 0.4.5 on Mac OS X with MySQL 5.1. Thanks for any help! It's a server configuration issue- the server is using a legacy compatibility mode. Check the mysql docs for configuring sql mode to a modern, strict setting. --~--~-~--~~~---~--~~ 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] Combining joined and single table inheritance
Hi,
is it possible to combine joined and single table inheritance in the
same inheritance hierarchy?
In the code at the bottom I want to use the persons table as a base
for the Person, Employee and Manager classes and the employees table
to store the particular fields of Employee and Manager.
I've run this code using PostgreSql and it correctly flushes the
Person and Employee instances but when it wants to flush the Manager
instance gives the following error:
/home/fernando/.pythonrepo/SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/
engine/base.py in _handle_dbapi_exception(self, e, statement,
parameters, cursor)
940 if self.__close_with_result:
941 self.close()
-- 942 raise exceptions.DBAPIError.instance(statement,
parameters, e, connection_invalidated=is_disconnect)
943 finally:
944 del self._reentrant_error
class 'sqlalchemy.exceptions.ProgrammingError': (ProgrammingError)
relation employees_person_id_seq does not exist
'select nextval(\'employees_person_id_seq\')' None
Anyone have any suggestions?
thanks,
Fernando
class Person(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return self.__class__.__name__ + + self.name
class Employee(Person):
def __init__(self, name, employee_data):
self.name = name
self.employee_data = employee_data
def __repr__(self):
return self.__class__.__name__ + + self.name + +
self.employee_data
class Manager(Employee):
def __init__(self, name, employee_data, manager_data):
self.name = name
self.employee_data = employee_data
self.manager_data = manager_data
def __repr__(self):
return self.__class__.__name__ + + self.name + +
self.manager_data
persons_table = Table('persons', metadata,
Column('person_id', Integer, primary_key=True),
Column('name', String(50)),
Column('type', String(20), nullable=False)
)
employees_table = Table('employees', metadata,
Column('person_id', Integer, ForeignKey('persons.person_id'),
primary_key=True),
Column('employee_data', String(50)),
Column('manager_data', String(50)),
)
person_mapper = mapper(Person, persons_table, \
polymorphic_on=persons_table.c.type,
polymorphic_identity='person')
employee_mapper = mapper(Employee, employees_table, inherits=Person,
polymorphic_identity='engineer')
manager_mapper = mapper(Manager, inherits=employee_mapper,
polymorphic_identity='manager')
metadata.create_all(engine);
Session = scoped_session(sessionmaker(bind=engine, autoflush=True,
transactional=True))
session = Session();
session.save(Person('fer'))
session.save(Employee('e', 'e1'))
session.save(Manager('m', 'e2', 'm1'))
session.flush()
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
