[sqlalchemy] Re: Direct execute of postgres COPY
On May 22, 8:37 pm, schickb <[EMAIL PROTECTED]> wrote: > Maybe SA is putting second execute in a transaction that isn't > being commited? That was the problem. I found this in the SA docs: "While many DBAPIs implement a flag called autocommit, the current SQLAlchemy behavior is such that it implements its own autocommit. This is achieved by searching the statement for strings like INSERT, UPDATE, DELETE, etc. and then issuing a COMMIT automatically if no transaction is in progress." I fixed the issue by wrapping the execute with trans = dbconn.begin() before and trans.commit() after. Seems like the decision to autocommit raw sql statements would be better left to the DBAPIs since they know more about non-standard sql statements like COPY. -Brad --~--~-~--~~~---~--~~ 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: Connecting to an MS SQL server ?
On Thu, May 22, 2008 at 1:20 PM, TkNeo <[EMAIL PROTECTED]> wrote: > > manager. you know how it is... > > i waste a lot of time , as you can see, because of being stuck to 2.3 > and half the libraries out there are for 2.4 onwards. I think one day > they will realize this and let me upgrade. > > > so there is no way out for us 2.3 users... > are u on linux? use virtual environment in your username? http://www.lucasmanual.com/mywiki/TurboGears#head-36fb4094da01b8c28e8bdca803c0f05774eb13b8 Lucas > > > > > On May 22, 10:38 am, Michael Bayer <[EMAIL PROTECTED]> wrote: >> On May 22, 2008, at 11:25 AM,TkNeowrote: >> >> >> >> > I get the following error. After this i tried installing pymssql which >> > requires a minimum of python 2.4 and all i have is python 2.3 >> >> > Any way out for us python 2.3 users ? >> >> why cant you get onto py2.5 ? > > > -- 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] Direct execute of postgres COPY
I'm brand new to SA, and trying to import data from a text file by
directly executing postgresql's COPY statement. It works fine in the
postgresql client (psql), but when I run the same statements through
SA I get an empty table. This is a snip of the python code:
dbengine = create_engine('postgres://%s:[EMAIL PROTECTED]:5432/database' %
(admin, passwd))
dbengine.echo = True
dbconn = dbengine.connect()
dbconn.execute("DELETE FROM facility")
dbconn.execute("COPY facility FROM '" + abspath(output_file) + "' WITH
DELIMITER '|' NULL ''")
dbconn.close()
This is the echo text, which looks fine to me:
2008-05-22 20:14:46,726 INFO sqlalchemy.engine.base.Engine.0x..cL
DELETE FROM facility
2008-05-22 20:14:46,727 INFO sqlalchemy.engine.base.Engine.0x..cL {}
2008-05-22 20:14:46,746 INFO sqlalchemy.engine.base.Engine.0x..cL
COMMIT
2008-05-22 20:14:46,748 INFO sqlalchemy.engine.base.Engine.0x..cL COPY
facility FROM '/home/brad/facility.cln' WITH DELIMITER '|' NULL ''
2008-05-22 20:14:46,748 INFO sqlalchemy.engine.base.Engine.0x..cL {}
The DELETE statement clearly works since at this point my table has 0
rows. I know pgsql is doing something with the file because if there
is an error in the data I get an exception. And if I run the same
statements under the same user account in psql, it results in 39543
rows. Maybe SA is putting second execute in a transaction that isn't
being commited?
>>> sqlalchemy.__version__
'0.4.2p3'
$ psql --version
psql (PostgreSQL) 8.3.1
$ uname -a
Linux name 2.6.24-16-generic #1 SMP Thu Apr 10 13:23:42 UTC 2008 i686
GNU/Linux
Thanks for any pointers.
--~--~-~--~~~---~--~~
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: Possible quoting bug, SQLite? If not, what wrong (please)?
the case solution works too, which is what i'll go with. 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: Possible quoting bug, SQLite? If not, what wrong (please)?
1 - changing "isnull" to "foo" worked. thanks!
2 - i tried adding text('nulls last') in the order_by(...), but got an
error from sqlite. i don't suppose there's something clever in
sqlalchemy that does this in a portable way is there?
3 - what do you use as a reference for SQL? do you have the ansi
standard? can you recommend a good book? i think i need a
reference...
cheers and thanks again,
andrew
--~--~-~--~~~---~--~~
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: Possible quoting bug, SQLite? If not, what wrong (please)?
label() is quoting "isnull" because "isnull" is listed as a reserved
word. literal_column(isnull) is not, because literal_column does
exactly what you say.
So id try first not using the word "isnull" for the label. After
that, its possible that SQlite isnt going to let you allow ordering by
a label, its not standard SQL.
what you're really looking for here is "order by NULLS
FIRST". see if sqlite supports that.
the next thing to try is "order by CASE WHEN colname IS NULL then
"A" else colname" - that can also be textual or you can use the
case() construct.
On May 22, 2008, at 8:43 PM, andrew cooke wrote:
>
>
> Hi,
>
> I have a rather complex query, which may be incorrect (I end up
> needing to use a literal column, which suggests I am doing something
> wrong). Anyway, it does work OK in MySQL, but when I switch to
> SQLite, I see the following error (see below, formatted for easier
> reading).
>
> Is this my fault, or is it a bug? Maybe "as" is not intended for use
> here (which I need to use the literal column in the other by later).
> If so, how would I do this?
>
> Thanks,
> Andrew
>
> PS Incidentally, the reason I need "isnull" is to force the ordering
> of NULL to come after the ordered numeric values. See
> http://www.shawnolson.net/a/730/mysql-sort-order-with-null.html If
> there's a better approach to solving that issue then I could avoid
> this.
>
> (OperationalError) near "isnull": syntax error
> u'SELECT rules.classification_id,
> rules.upper_bound_inclusive IS NULL AS "isnull"
>FROM (
> SELECT standards.standard_id AS standard_id
>FROM standards
> WHERE standards.metric_id = ?
> AND (standards.end_date_inclusive > ?
> OR standards.end_date_inclusive IS NULL)
> ORDER BY standards.end_date_inclusive DESC
> LIMIT 1 OFFSET 0
>) AS anon_1
>JOIN rules ON anon_1.standard_id = rules.standard_id
> WHERE rules.upper_bound_inclusive >= ?
> OR rules.upper_bound_inclusive IS NULL
> ORDER BY isnull ASC, rules.upper_bound_inclusive ASC
> LIMIT 1 OFFSET 0' [...params here...]
>
> Note the quotes around isnull near the start.
>
> The Python code I used was:
>
># parameters needed on call
>p_metric = bindparam('metric')
>p_date = bindparam('date')
>p_value = bindparam('value')
># tables references
>stds = self.__context.t.standard
>rules = self.__context.t.rule
># incrementally construct the query
># first, we want the latest standard for the metric
>std_id = select([stds.c.standard_id], stds.c.metric_id ==
> p_metric)
># with a date valid until after the measurement
>std_id = std_id.where(or_(stds.c.end_date_inclusive >
> p_date,
> stds.c.end_date_inclusive ==
> None))
># and we want the first of those
>std_id =
> std_id.order_by(stds.c.end_date_inclusive.desc()).limit(1)
># now we want the rules associated with that standard
># (the alias below seems to be a mysql oddity? without it
> we
># get an error)
>cls_id = select(
>[rules.c.classification_id,
>
> (rules.c.upper_bound_inclusive==None).label(isnull)],
>from_obj=std_id.alias().join(rules))
># but only rules that have an upper bound above the value
>cls_id = cls_id.where(or_(rules.c.upper_bound_inclusive >=
> p_value,
> rules.c.upper_bound_inclusive ==
> None))
># and again, sort those so that we get the lowest upper
> bound
>cls_id = cls_id.order_by(literal_column(isnull).asc(),
>
> rules.c.upper_bound_inclusive.asc())
>cls_id = cls_id.limit(1)
>
>
> >
--~--~-~--~~~---~--~~
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: Possible quoting bug, SQLite? If not, what wrong (please)?
Just to clarify, the "isnull" that is causing the problem is introduced by (rules.c.upper_bound_inclusive==None).label(isnull) (I implied it was with as()). Andrew --~--~-~--~~~---~--~~ 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] Possible quoting bug, SQLite? If not, what wrong (please)?
Hi,
I have a rather complex query, which may be incorrect (I end up
needing to use a literal column, which suggests I am doing something
wrong). Anyway, it does work OK in MySQL, but when I switch to
SQLite, I see the following error (see below, formatted for easier
reading).
Is this my fault, or is it a bug? Maybe "as" is not intended for use
here (which I need to use the literal column in the other by later).
If so, how would I do this?
Thanks,
Andrew
PS Incidentally, the reason I need "isnull" is to force the ordering
of NULL to come after the ordered numeric values. See
http://www.shawnolson.net/a/730/mysql-sort-order-with-null.html If
there's a better approach to solving that issue then I could avoid
this.
(OperationalError) near "isnull": syntax error
u'SELECT rules.classification_id,
rules.upper_bound_inclusive IS NULL AS "isnull"
FROM (
SELECT standards.standard_id AS standard_id
FROM standards
WHERE standards.metric_id = ?
AND (standards.end_date_inclusive > ?
OR standards.end_date_inclusive IS NULL)
ORDER BY standards.end_date_inclusive DESC
LIMIT 1 OFFSET 0
) AS anon_1
JOIN rules ON anon_1.standard_id = rules.standard_id
WHERE rules.upper_bound_inclusive >= ?
OR rules.upper_bound_inclusive IS NULL
ORDER BY isnull ASC, rules.upper_bound_inclusive ASC
LIMIT 1 OFFSET 0' [...params here...]
Note the quotes around isnull near the start.
The Python code I used was:
# parameters needed on call
p_metric = bindparam('metric')
p_date = bindparam('date')
p_value = bindparam('value')
# tables references
stds = self.__context.t.standard
rules = self.__context.t.rule
# incrementally construct the query
# first, we want the latest standard for the metric
std_id = select([stds.c.standard_id], stds.c.metric_id ==
p_metric)
# with a date valid until after the measurement
std_id = std_id.where(or_(stds.c.end_date_inclusive >
p_date,
stds.c.end_date_inclusive ==
None))
# and we want the first of those
std_id =
std_id.order_by(stds.c.end_date_inclusive.desc()).limit(1)
# now we want the rules associated with that standard
# (the alias below seems to be a mysql oddity? without it
we
# get an error)
cls_id = select(
[rules.c.classification_id,
(rules.c.upper_bound_inclusive==None).label(isnull)],
from_obj=std_id.alias().join(rules))
# but only rules that have an upper bound above the value
cls_id = cls_id.where(or_(rules.c.upper_bound_inclusive >=
p_value,
rules.c.upper_bound_inclusive ==
None))
# and again, sort those so that we get the lowest upper
bound
cls_id = cls_id.order_by(literal_column(isnull).asc(),
rules.c.upper_bound_inclusive.asc())
cls_id = cls_id.limit(1)
--~--~-~--~~~---~--~~
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: Connecting to an MS SQL server ?
TkNeo wrote: > I am trying to connect to an MS SQL server 2000 ... > using Integrated Security and not use a specific username and > password. Can anyone tell me the format of the connection string ? ... > I tried installing pymssql which requires a minimum of python 2.4 > and all I have is python 2.3. Any way out for us python 2.3 users ? Even if you could get PyMssql to work, you'd still be stuck since it doesn't support Integrated Security/Trusted Connection. Instead, you could try PyOdbc. The current version requires Python 2.4 in order to support Decimal types, however. The oldest version listed on http://pyodbc.sourceforge.net/ (from 2006) is still for Python 2.4. http://adodbapi.sourceforge.net/ says it works on Python 2.3. I don't know if it will support Integrated Security. --~--~-~--~~~---~--~~ 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: join two selects
Thank you for the quick and very helpful response. I'm making progress again! Jeff. On May 22, 2008, at 3:50 PM, Michael Bayer wrote: > > > On May 22, 2008, at 6:44 PM, Jeff Putsch wrote: > >> >> >> On May 22, 2008, at 3:08 PM, Michael Bayer wrote: >> >>> a1.join(a2, ) should do it. if not, supply a full test >>> case >>> and a description of the specific problem. >> >> OK, I guess. > > we need more detail than "it fails" to have a clue what the issue > might be is > >> So how do I see the SQL that gets generated? >> >> I've tried this: >> >> print select(from_obj=[a1.join(a2), a1.c.eid == a2.c.eid]) > > its not a pretty error message, but the main idea there is that "x==y" > is not a FROM object. to join on an explciit ON clause, its like > this: > > a1.join(a2, a1.c.eid==a2.c.eid) > > and the "print" will work there. > > > > > --~--~-~--~~~---~--~~ 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: join two selects
I apologize for this second, longer, more detailed, post, but I
thought my first response
to the request for more descriptions was incomplete...
On May 22, 3:08 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> a1.join(a2, ) should do it. if not, supply a full test case
> and a description of the specific problem.
The database tables were defined elsewhere, via
code like this:
nis_user = Table('nis_users', meta,
Column('id', Integer, primary_key = True),
Column('eid', String(32), default=""),
Column('uid', Integer, nullable = False),
Column('uname', String(256), nullable = False),
UniqueConstraint('eid', 'uid', 'uname')
)
nis_account = Table('nis_accounts', meta,
Column('id', Integer, primary_key = True),
Column('domain_id', Integer, ForeignKey('domains.id')),
Column('nis_user_id', Integer, nullable = False),
Column('gid', Integer, default=60001),
Column('gcos', String(256)),
Column('shell', String(256)),
Column('home', String(256)),
Column('terminated', Boolean, default = False),
Column('reassigned_uid', Boolean, default = False),
Column('active', Boolean, default = True),
UniqueConstraint('domain_id', 'nis_user_id'),
ForeignKeyConstraint(['nis_user_id'],['nis_users.id'],
ondelete="CASCADE")
)
So here's the Python Code:
nis_users_table = Table('nis_users', metadata, autoload=True)
nis_accounts_table = Table('nis_accounts', metadata, autoload=True)
class NisAccount(object):
pass
class NisUser(object):
pass
mapper(NisUser, nis_users_table, properties = {
'accounts':relation(NisAccount,
primaryjoin=nis_users_table.c.id ==
nis_accounts_table.c.nis_user_id,
backref='user',
lazy=False)
},
order_by = nis_users_table.c.uid
)
mapper(NisAccount, nis_accounts_table, properties={
'uid' : column_property(
select(
[nis_users_table.c.uid],
nis_users_table.c.id ==
nis_accounts_table.c.nis_user_id
).correlate(nis_accounts_table).label('uid')
),
'uname' : column_property(
select(
[nis_users_table.c.uname],
nis_users_table.c.id ==
nis_accounts_table.c.nis_user_id
).correlate(nis_accounts_table).label('uname')
),
'eid' : column_property(
select(
[nis_users_table.c.eid],
nis_users_table.c.id ==
nis_accounts_table.c.nis_user_id
).correlate(nis_accounts_table).label('eid')
)
}
)
So, when I do
s = select([nis_accounts_table, nis_users_table],
from_obj=[nis_accounts_table.join(nis_users_table)]).where(nis_users_table.
c.eid != '')
I get the SQL query I expect.
Then I do:
a1 = s.correlate(None).alias()
a2 = s.correlate(None).alias()
Now, trying to make a new select:
s2 = select(from_obj=[a1.join(a2), a1.c.eid == a2.c.eid])
Gives me a object
When I do:
print s2
I get:
Traceback (most recent call last):
File "", line 1, in ?
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/expression.py", line
1136,
in __str__
return unicode(self.compile()).encode('ascii',
'backslashreplace')
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/expression.py", line
1132,
in compile
compiler.compile()
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/compiler.py", line 181,
in
compile
self.string = self.process(self.statement)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/compiler.py", line 189,
in
process
return meth(obj, **kwargs)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/compiler.py", line 491,
in
visit_select
froms = select._get_display_froms(existingfroms)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/expression.py", line
3034,
in _get_display_froms
toremove = itertools.chain(*[f._hide_froms for f in froms])
AttributeError: '_BinaryExpression' object has no attribute
'_hide_froms'
So the question is twofold:
1. What am I doing wrong?
2. How do I see the SQL that would be generated if I'm doing nothing
wrong?
Jeff.
--~--~-~--~~~---~--~~
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: join two selects
On May 22, 2008, at 6:44 PM, Jeff Putsch wrote: > > > On May 22, 2008, at 3:08 PM, Michael Bayer wrote: > >> a1.join(a2, ) should do it. if not, supply a full test >> case >> and a description of the specific problem. > > OK, I guess. we need more detail than "it fails" to have a clue what the issue might be is > So how do I see the SQL that gets generated? > > I've tried this: > >print select(from_obj=[a1.join(a2), a1.c.eid == a2.c.eid]) its not a pretty error message, but the main idea there is that "x==y" is not a FROM object. to join on an explciit ON clause, its like this: a1.join(a2, a1.c.eid==a2.c.eid) and the "print" will work there. --~--~-~--~~~---~--~~ 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: foreign_keys and _local_remote_pairs problem in relation
Worked like a charm. Thank you for such a prompt reply and for the
project in general.
-brad
On May 22, 6:36 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On May 22, 2008, at 6:10 PM, kremlan wrote:
>
>
>
>
>
> > I have the following setup:
> > (relevant excerpts only)
>
> > contacts = Table('contacts', meta,
> >Column('id', Integer, primary_key=True),
> >Column('display_as', String(75)),
> >Column('title', String(5)),
> >Column('first_name', String(25)),
> >Column('middle_name', String(25)),
> >Column('last_name', String(25)),
> >Column('suffix', String(5)),
> >Column('job_title', String(50)),
> >Column('department', String(50)),
> >Column('company', String(50)),
> >Column('gender', String(1)),
> >Column('website', String(100)),
> >Column('notes', Text),
> >Column('active', Boolean),
> >Column('account_id', Integer),
> >Column('time_zone_id', Integer),
> >Column('created_at', DateTime),
> >Column('updated_at', DateTime),
> >Column('created_by', Integer),
> >Column('updated_by', Integer),
> >ForeignKeyConstraint(['account_id'], ['accounts.id']),
> >ForeignKeyConstraint(['time_zone_id'], ['time_zones.id']),
> >ForeignKeyConstraint(['created_by'], ['contacts.id']),
> >ForeignKeyConstraint(['updated_by'], ['contacts.id'])
> > )
>
> > payment_methods = Table('payment_methods', meta,
> >Column('id', Integer, primary_key=True),
> >Column('contact_id', Integer),
> >Column('payment_method_type_id', Integer),
> >Column('created_at', DateTime),
> >Column('updated_at', DateTime),
> >Column('created_by', Integer),
> >Column('updated_by', Integer),
> >ForeignKeyConstraint(['contact_id'],['contacts.id']),
> >ForeignKeyConstraint(['payment_method_type_id'],
> > ['payment_method_types.id']),
> >ForeignKeyConstraint(['created_by'],['contacts.id']),
> >ForeignKeyConstraint(['updated_by'],['contacts.id']),
> > )
>
> > class Contact(object):
> >pass
>
> > class PaymentMethod(object):
> >pass
>
> > mapper(Contact, contacts, extension=HistoryMapperExtension(),
> > properties={
> >'payment_methods': relation(PaymentMethod,
> > backref='contact',
>
> > primaryjoin=payment_methods.c.contact_id,
>
> > _local_remote_pairs=[(contacts.c.id, payment_methods.c.contact_id)],
>
> > foreign_keys=[payment_methods.c.contact_id],
>
> > backref='contact')
> >})
>
> > mapper(PaymentMethod, payment_methods)
>
> > A series of exceptions led me to add the primaryjoin, then
> > foreign_keys, then _local_remote_pairs options. Once all three were in
> > place I then received another ArgumentError exception advising I
> > specify a foreign_keys option.
>
> dont use _local_remote_pairs. its underscored because its pretty
> experimental, and i should probably remove it from the error message
> there (im surprised its in there...well there it is...erg).
>
> primaryjoin needs to reference a SQL expression that joins the two
> tables together, as in primaryjoin =
> tablea.c.somecolumn==tableb.c.someothercolumn (this is documented
> here:
> http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio...
> ) . Your Table objects already have ForeignKey(Constraint) objects
> set up so that should be all you need.
--~--~-~--~~~---~--~~
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: join two selects
On May 22, 2008, at 3:08 PM, Michael Bayer wrote:
> a1.join(a2, ) should do it. if not, supply a full test case
> and a description of the specific problem.
OK, I guess. So how do I see the SQL that gets generated?
I've tried this:
print select(from_obj=[a1.join(a2), a1.c.eid == a2.c.eid])
And get:
Traceback (most recent call last):
File "", line 1, in ?
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/expression.py", line 1136,
in __str__
return unicode(self.compile()).encode('ascii', 'backslashreplace')
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/expression.py", line 1132,
in compile
compiler.compile()
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/compiler.py", line 181, in
compile
self.string = self.process(self.statement)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/compiler.py", line 189, in
process
return meth(obj, **kwargs)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/compiler.py", line 491, in
visit_select
froms = select._get_display_froms(existingfroms)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/sql/expression.py", line 3034,
in _get_display_froms
toremove = itertools.chain(*[f._hide_froms for f in froms])
AttributeError: '_BinaryExpression' object has no attribute
'_hide_froms'
--~--~-~--~~~---~--~~
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: foreign_keys and _local_remote_pairs problem in relation
On May 22, 2008, at 6:10 PM, kremlan wrote:
>
> I have the following setup:
> (relevant excerpts only)
>
> contacts = Table('contacts', meta,
>Column('id', Integer, primary_key=True),
>Column('display_as', String(75)),
>Column('title', String(5)),
>Column('first_name', String(25)),
>Column('middle_name', String(25)),
>Column('last_name', String(25)),
>Column('suffix', String(5)),
>Column('job_title', String(50)),
>Column('department', String(50)),
>Column('company', String(50)),
>Column('gender', String(1)),
>Column('website', String(100)),
>Column('notes', Text),
>Column('active', Boolean),
>Column('account_id', Integer),
>Column('time_zone_id', Integer),
>Column('created_at', DateTime),
>Column('updated_at', DateTime),
>Column('created_by', Integer),
>Column('updated_by', Integer),
>ForeignKeyConstraint(['account_id'], ['accounts.id']),
>ForeignKeyConstraint(['time_zone_id'], ['time_zones.id']),
>ForeignKeyConstraint(['created_by'], ['contacts.id']),
>ForeignKeyConstraint(['updated_by'], ['contacts.id'])
> )
>
> payment_methods = Table('payment_methods', meta,
>Column('id', Integer, primary_key=True),
>Column('contact_id', Integer),
>Column('payment_method_type_id', Integer),
>Column('created_at', DateTime),
>Column('updated_at', DateTime),
>Column('created_by', Integer),
>Column('updated_by', Integer),
>ForeignKeyConstraint(['contact_id'],['contacts.id']),
>ForeignKeyConstraint(['payment_method_type_id'],
> ['payment_method_types.id']),
>ForeignKeyConstraint(['created_by'],['contacts.id']),
>ForeignKeyConstraint(['updated_by'],['contacts.id']),
> )
>
> class Contact(object):
>pass
>
> class PaymentMethod(object):
>pass
>
>
> mapper(Contact, contacts, extension=HistoryMapperExtension(),
> properties={
>'payment_methods': relation(PaymentMethod,
> backref='contact',
>
> primaryjoin=payment_methods.c.contact_id,
>
> _local_remote_pairs=[(contacts.c.id, payment_methods.c.contact_id)],
>
> foreign_keys=[payment_methods.c.contact_id],
>
> backref='contact')
>})
>
> mapper(PaymentMethod, payment_methods)
>
> A series of exceptions led me to add the primaryjoin, then
> foreign_keys, then _local_remote_pairs options. Once all three were in
> place I then received another ArgumentError exception advising I
> specify a foreign_keys option.
dont use _local_remote_pairs. its underscored because its pretty
experimental, and i should probably remove it from the error message
there (im surprised its in there...well there it is...erg).
primaryjoin needs to reference a SQL expression that joins the two
tables together, as in primaryjoin =
tablea.c.somecolumn==tableb.c.someothercolumn (this is documented
here:
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_customjoin
) . Your Table objects already have ForeignKey(Constraint) objects
set up so that should be all you need.
--~--~-~--~~~---~--~~
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] foreign_keys and _local_remote_pairs problem in relation
I have the following setup:
(relevant excerpts only)
contacts = Table('contacts', meta,
Column('id', Integer, primary_key=True),
Column('display_as', String(75)),
Column('title', String(5)),
Column('first_name', String(25)),
Column('middle_name', String(25)),
Column('last_name', String(25)),
Column('suffix', String(5)),
Column('job_title', String(50)),
Column('department', String(50)),
Column('company', String(50)),
Column('gender', String(1)),
Column('website', String(100)),
Column('notes', Text),
Column('active', Boolean),
Column('account_id', Integer),
Column('time_zone_id', Integer),
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('created_by', Integer),
Column('updated_by', Integer),
ForeignKeyConstraint(['account_id'], ['accounts.id']),
ForeignKeyConstraint(['time_zone_id'], ['time_zones.id']),
ForeignKeyConstraint(['created_by'], ['contacts.id']),
ForeignKeyConstraint(['updated_by'], ['contacts.id'])
)
payment_methods = Table('payment_methods', meta,
Column('id', Integer, primary_key=True),
Column('contact_id', Integer),
Column('payment_method_type_id', Integer),
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('created_by', Integer),
Column('updated_by', Integer),
ForeignKeyConstraint(['contact_id'],['contacts.id']),
ForeignKeyConstraint(['payment_method_type_id'],
['payment_method_types.id']),
ForeignKeyConstraint(['created_by'],['contacts.id']),
ForeignKeyConstraint(['updated_by'],['contacts.id']),
)
class Contact(object):
pass
class PaymentMethod(object):
pass
mapper(Contact, contacts, extension=HistoryMapperExtension(),
properties={
'payment_methods': relation(PaymentMethod,
backref='contact',
primaryjoin=payment_methods.c.contact_id,
_local_remote_pairs=[(contacts.c.id, payment_methods.c.contact_id)],
foreign_keys=[payment_methods.c.contact_id],
backref='contact')
})
mapper(PaymentMethod, payment_methods)
A series of exceptions led me to add the primaryjoin, then
foreign_keys, then _local_remote_pairs options. Once all three were in
place I then received another ArgumentError exception advising I
specify a foreign_keys option.
Full exception text:
ArgumentError: Could not determine relation direction for primaryjoin
condition 'payment_methods.contact_id', on relation
PaymentMethod.contact (Contact). Specify the foreign_keys argument to
indicate which columns on the relation are foreign.
Any help would be appreciated.
-brad
--~--~-~--~~~---~--~~
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: join two selects
On May 22, 2008, at 5:51 PM, Jeff Putsch wrote: > Which can give me two selects using aliases: > > a1 = s.correlate(None).alias() > a2 = s.correlate(None).alias() > > But every attempt at using a1 and a2 in a select with a join is > failing for me. a1.join(a2, ) should do it. if not, supply a full test case and a description of the specific problem. --~--~-~--~~~---~--~~ 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] join two selects
Howdy, I've got this SQL query: select * from ( select a1.domain_id as domain_id_1, u1.eid as eid, u1.uname as uname, u1.uid as uid, a1.gcos, a1.home, a1.shell from nis_accounts a1 inner join nis_users u1 on (a1.nis_user_id = u1.id) where u1.eid != '' ) ndu1 inner join ( select a2.domain_id as domain_id, u2.eid as eid, u2.uname as uname, u2.uid as uid, a2.gcos, a2.home, a2.shell from nis_accounts a2 inner join nis_users u2 on (a2.nis_user_id = u2.id) where u2.eid != '' ) ndu2 on (ndu1.eid = ndu2.eid and ndu1.uid != ndu2.uid) where ndu1.eid in ( select eid from nis_users group by eid having count(uid) > 1 and eid != '' and uname not like '%_old' ) and domain_id_1 = 45 And am trying to represent it in sqlalchemy python speak. I can get ( select a2.domain_id as domain_id, u2.eid as eid, u2.uname as uname, u2.uid as uid, a2.gcos, a2.home, a2.shell from nis_accounts a2 inner join nis_users u2 on (a2.nis_user_id = u2.id) where u2.eid != '' ) ndu2 translated to: s = select([nis_accounts_table, nis_users_table], from_obj=[nis_accounts_table.join(nis_users_table)]).where(nis_users_table.c.eid ! = '') Which can give me two selects using aliases: a1 = s.correlate(None).alias() a2 = s.correlate(None).alias() But every attempt at using a1 and a2 in a select with a join is failing for me. I'm sure I'm missing something here. Help and pointers will be greatly appreciated. Thanks, Jeff. --~--~-~--~~~---~--~~ 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] how to represent this?
hi object A has 2 columns, x and y. From all the A instances, for each distinct x, i want to get the instance that has maximum y. (it's a temporal query, x is obj_id, y is time - yielding the latest version of all the objects) so far i invented this sql: select a.* from a, (select x,max(y) as y from a group by x) as r where a.x==r.x and a.y==r.y; (it is a bit weak relying on joining on y-value...) and in SA: class A: ... atable = class_mapper(A).local_table r = select( [ atable.c.x.label( 'mx'), func.max( atable.c.y).label( 'my')] ) .group_by( atable.c.x) q = session.query(A).filter( (A.x==r.c.mx) & (A.y==r.c.my) ) can it be done neater? e.g. without separate select/atable... thanks 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: Using select clause for column default
On May 22, 2008, at 4:44 PM, askel wrote: > > I implemented it the way Michael suggested. Now, I'm wondering if it > can be done as a subquery, i.e. avoiding pre-execution of select > clause. May be that is pretty much SQL engine specific but so are > sequences and foreign keys. Resulting query built by compiler should > be like the following: > > INSERT INTO accounts (group_id, number) VALUES ((SELECT id FROM groups > WHERE prefix=:prefix), :number) > > prefix and number are bind parameters. number came from > accounts.insert() and prefix is produced by column default function > based on number parameter. yeah, the plumbing doesn't connect exactly that way at the moment, as far as a Column-level default is concerned. The parameters used for execution are not available at insert() compile time, that was a coupling we broke off in 0.4 which greatly simplified things. I think you already know you can do it at the ORM level though by setting the object's attribute to a SQL expression, then flushing - the mapper embeds the expression inline. You could set that up in the __init__ method of your class to make it a "default". --~--~-~--~~~---~--~~ 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: Using select clause for column default
I implemented it the way Michael suggested. Now, I'm wondering if it
can be done as a subquery, i.e. avoiding pre-execution of select
clause. May be that is pretty much SQL engine specific but so are
sequences and foreign keys. Resulting query built by compiler should
be like the following:
INSERT INTO accounts (group_id, number) VALUES ((SELECT id FROM groups
WHERE prefix=:prefix), :number)
prefix and number are bind parameters. number came from
accounts.insert() and prefix is produced by column default function
based on number parameter.
On May 22, 12:37 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On May 22, 2008, at 12:23 PM, askel wrote:
>
>
>
>
>
> > Hello everybody,
>
> > I'm having hard time figuring out how or whether it is possible at all
> > to use select statement that can access record to be inserted as
> > column's default value.
>
> > groups = Table('groups', meta,
> >Column('id', Integer, primary_key=True),
> >Column('prefix', String(32), nullable=False, unique=True)
> > )
>
> > accounts = Table('accounts', meta,
> >Column('number', String(32), primary_key=True),
> >Column('group_id', Integer, ForeignKey('groups.id'),
> > nullable=False,
> >default=select([groups.c.id], groups.c.prefix ==
> > somefunc(current_accounts_record)))
> > )
>
> > Basically, what I need is to be able to access record/values to be
> > inserted into accounts table to build correct select clause. I know
> > how to do that on ORM level using MapperExtension but I want to
> > enforce this on table level instead. And I realize that I can
> > explicitly call my function to assign value to group_id at the time
> > accounts.insert is executed but that doesn't smell good.
>
> > Any help is greatly appreciated.
>
> the function you pass to default can take an optional "context"
> parameter which contains the current ExecutionContext. so write it
> like this:
>
> def my_default(ctx):
> current_accounts_record = ctx.parameters['some_parameter']
> return ctx.connection.scalar(select([groups.c.id],
> groups.c.prefix ==somefunc(current_accounts_record)))
>
> ...
>
> Column('mycolumn', Integer, default=my_default)
--~--~-~--~~~---~--~~
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: Using select clause for column default
Thank you Michael
On May 22, 12:37 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On May 22, 2008, at 12:23 PM, askel wrote:
>
>
>
>
>
> > Hello everybody,
>
> > I'm having hard time figuring out how or whether it is possible at all
> > to use select statement that can access record to be inserted as
> > column's default value.
>
> > groups = Table('groups', meta,
> >Column('id', Integer, primary_key=True),
> >Column('prefix', String(32), nullable=False, unique=True)
> > )
>
> > accounts = Table('accounts', meta,
> >Column('number', String(32), primary_key=True),
> >Column('group_id', Integer, ForeignKey('groups.id'),
> > nullable=False,
> >default=select([groups.c.id], groups.c.prefix ==
> > somefunc(current_accounts_record)))
> > )
>
> > Basically, what I need is to be able to access record/values to be
> > inserted into accounts table to build correct select clause. I know
> > how to do that on ORM level using MapperExtension but I want to
> > enforce this on table level instead. And I realize that I can
> > explicitly call my function to assign value to group_id at the time
> > accounts.insert is executed but that doesn't smell good.
>
> > Any help is greatly appreciated.
>
> the function you pass to default can take an optional "context"
> parameter which contains the current ExecutionContext. so write it
> like this:
>
> def my_default(ctx):
> current_accounts_record = ctx.parameters['some_parameter']
> return ctx.connection.scalar(select([groups.c.id],
> groups.c.prefix ==somefunc(current_accounts_record)))
>
> ...
>
> Column('mycolumn', Integer, default=my_default)
--~--~-~--~~~---~--~~
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: Connecting to an MS SQL server ?
manager. you know how it is... i waste a lot of time , as you can see, because of being stuck to 2.3 and half the libraries out there are for 2.4 onwards. I think one day they will realize this and let me upgrade. so there is no way out for us 2.3 users... On May 22, 10:38 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On May 22, 2008, at 11:25 AM,TkNeowrote: > > > > > I get the following error. After this i tried installing pymssql which > > requires a minimum of python 2.4 and all i have is python 2.3 > > > Any way out for us python 2.3 users ? > > why cant you get onto py2.5 ? --~--~-~--~~~---~--~~ 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: Using select clause for column default
On May 22, 2008, at 12:23 PM, askel wrote:
>
> Hello everybody,
>
> I'm having hard time figuring out how or whether it is possible at all
> to use select statement that can access record to be inserted as
> column's default value.
>
> groups = Table('groups', meta,
>Column('id', Integer, primary_key=True),
>Column('prefix', String(32), nullable=False, unique=True)
> )
>
> accounts = Table('accounts', meta,
>Column('number', String(32), primary_key=True),
>Column('group_id', Integer, ForeignKey('groups.id'),
> nullable=False,
>default=select([groups.c.id], groups.c.prefix ==
> somefunc(current_accounts_record)))
> )
>
> Basically, what I need is to be able to access record/values to be
> inserted into accounts table to build correct select clause. I know
> how to do that on ORM level using MapperExtension but I want to
> enforce this on table level instead. And I realize that I can
> explicitly call my function to assign value to group_id at the time
> accounts.insert is executed but that doesn't smell good.
>
> Any help is greatly appreciated.
the function you pass to default can take an optional "context"
parameter which contains the current ExecutionContext. so write it
like this:
def my_default(ctx):
current_accounts_record = ctx.parameters['some_parameter']
return ctx.connection.scalar(select([groups.c.id],
groups.c.prefix ==somefunc(current_accounts_record)))
...
Column('mycolumn', Integer, default=my_default)
--~--~-~--~~~---~--~~
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] Using select clause for column default
Hello everybody,
I'm having hard time figuring out how or whether it is possible at all
to use select statement that can access record to be inserted as
column's default value.
groups = Table('groups', meta,
Column('id', Integer, primary_key=True),
Column('prefix', String(32), nullable=False, unique=True)
)
accounts = Table('accounts', meta,
Column('number', String(32), primary_key=True),
Column('group_id', Integer, ForeignKey('groups.id'),
nullable=False,
default=select([groups.c.id], groups.c.prefix ==
somefunc(current_accounts_record)))
)
Basically, what I need is to be able to access record/values to be
inserted into accounts table to build correct select clause. I know
how to do that on ORM level using MapperExtension but I want to
enforce this on table level instead. And I realize that I can
explicitly call my function to assign value to group_id at the time
accounts.insert is executed but that doesn't smell good.
Any help is greatly appreciated.
--~--~-~--~~~---~--~~
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: from_statement doesn't apply convert_unicode
On May 22, 2008, at 12:03 PM, Michael Bayer wrote:
>
> assuming you're talking about inbound parameters, not result
> setsuse bind parameters with from_statement, in conjunction with
> query.params():
>
>
> query.from_statement(text("select * from table where x
> =:y")).values(y=5).all()
sorry, i meant query.params(y=5)
--~--~-~--~~~---~--~~
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: from_statement doesn't apply convert_unicode
assuming you're talking about inbound parameters, not result
setsuse bind parameters with from_statement, in conjunction with
query.params():
query.from_statement(text("select * from table where x
=:y")).values(y=5).all()
On May 22, 2008, at 11:57 AM, Geoff wrote:
>
> Any know why results from using from_statement do not convert strings?
> It works fine when I use filter_by etc...
>
>
> >
--~--~-~--~~~---~--~~
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] from_statement doesn't apply convert_unicode
Any know why results from using from_statement do not convert strings? It works fine when I use filter_by etc... --~--~-~--~~~---~--~~ 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: Connecting to an MS SQL server ?
On May 22, 2008, at 11:25 AM, TkNeo wrote: > > I get the following error. After this i tried installing pymssql which > requires a minimum of python 2.4 and all i have is python 2.3 > > Any way out for us python 2.3 users ? why cant you get onto py2.5 ? --~--~-~--~~~---~--~~ 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: Connecting to an MS SQL server ?
I get the following error. After this i tried installing pymssql which
requires a minimum of python 2.4 and all i have is python 2.3
Any way out for us python 2.3 users ?
db = create_engine('mssql://wscmsql/ws market datasql.db')
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\__init__.py", line 160, in create_engine
return strategy.create(*args, **kwargs)
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\strategies.py", line 62, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\databases\mssql.py", line 452, in dbapi
raise ImportError('No DBAPI module detected for MSSQL - please
install pyodbc, pymssql, or adodbapi')
ImportError: No DBAPI module detected for MSSQL - please install
pyodbc, pymssql, or adodbapi
On May 15, 9:34 pm, "Lukasz Szybalski" <[EMAIL PROTECTED]> wrote:
> On Thu, May 15, 2008 at 12:51 PM, Yannick Gingras <[EMAIL PROTECTED]> wrote:
>
> >TkNeo<[EMAIL PROTECTED]> writes:
>
> >> Hi,
>
> > Hello Tarun,
>
> >> This is my first encounter with sqlalchemy. I am trying to connect to
> >> an MS SQL server 2000 that is not on local host. I want to connect
> >> using Integrated Security and not use a specific username and
> >> password. Can anyone tell me the format of the connection string ?
>
> > I don't know about Integrated Security but we use alchemy to connect
> > to a MSSQL from a GNU/Linux box and it works really well. We use Unix
> > ODBC with TDS with the DSN registered with the local ODBC.
>
> > Take a look at
>
> >http://www.lucasmanual.com/mywiki/TurboGears#head-4a47fe38beac67d9d03...
>
> > My obdb.ini looks like
>
> > [JDED]
> > Driver = TDS
> > Trace = No
> > Server = 192.168.33.53
> > Port= 1433
>
> > and my alchemy connection string is
>
> > mssql://user:pass@/?dsn=JDED&scope_identity=1
>
> Not sure what platform you are using but on linux I use:
> e = sqlalchemy.create_engine("mssql://user:[EMAIL
> PROTECTED]:1433/database?driver=TDS&odbc_options='TDS_Version=8.0'")
> but you need sa 0.4.6.
>
> on windows you can use:
> e = sqlalchemy.create_engine('mssql://user:[EMAIL PROTECTED]:1433/database')
>
> 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: Connecting to an MS SQL server ?
I get the following
db = create_engine('mssql://wscmsql/ws market datasql.db')
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\__init__.py", line 160, in create_engine
return strategy.create(*args, **kwargs)
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\strategies.py", line 62, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\databases\mssql.py", line 452, in dbapi
raise ImportError('No DBAPI module detected for MSSQL - please
install pyodbc, pymssql, or adodbapi')
ImportError: No DBAPI module detected for MSSQL - please install
pyodbc, pymssql, or adodbapi
The following exception gets raised in the create_engine
On May 15, 9:34 pm, "Lukasz Szybalski" <[EMAIL PROTECTED]> wrote:
> On Thu, May 15, 2008 at 12:51 PM, Yannick Gingras <[EMAIL PROTECTED]> wrote:
>
> >TkNeo<[EMAIL PROTECTED]> writes:
>
> >> Hi,
>
> > Hello Tarun,
>
> >> This is my first encounter with sqlalchemy. I am trying to connect to
> >> an MS SQL server 2000 that is not on local host. I want to connect
> >> using Integrated Security and not use a specific username and
> >> password. Can anyone tell me the format of the connection string ?
>
> > I don't know about Integrated Security but we use alchemy to connect
> > to a MSSQL from a GNU/Linux box and it works really well. We use Unix
> > ODBC with TDS with the DSN registered with the local ODBC.
>
> > Take a look at
>
> >http://www.lucasmanual.com/mywiki/TurboGears#head-4a47fe38beac67d9d03...
>
> > My obdb.ini looks like
>
> > [JDED]
> > Driver = TDS
> > Trace = No
> > Server = 192.168.33.53
> > Port= 1433
>
> > and my alchemy connection string is
>
> > mssql://user:pass@/?dsn=JDED&scope_identity=1
>
> Not sure what platform you are using but on linux I use:
> e = sqlalchemy.create_engine("mssql://user:[EMAIL
> PROTECTED]:1433/database?driver=TDS&odbc_options='TDS_Version=8.0'")
> but you need sa 0.4.6.
>
> on windows you can use:
> e = sqlalchemy.create_engine('mssql://user:[EMAIL PROTECTED]:1433/database')
>
> 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] mySQL force index?
Does SQLA have any mechanism to use FORCE INDEX? --~--~-~--~~~---~--~~ 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: Using a non-primary key as ORM identifier
Nice, thanks for the tips guys! From what I understand, MySQL has some tasty optimisations that are used when the primary key is an int. So once it knows what primary key it's looking for (after looking in the index), it's faster to retrieve the row. On May 22, 2:50 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On May 22, 2008, at 7:10 AM, Geoff wrote: > > > > > Hi, > > > I have split up my database horizontally and am using UUIDs to > > uniquely identify a row across databases. Using UUIDs as a primary key > > is slow (InnoDB) so I wanted to use the common trick of having a INT > > primary key using auto_increment on each database. This is all fine, > > until sqlalchemy checks its cache of objects after a query to see if > > the object has already been retrieved. This breaks because the primary > > key is not unique across databases when I use an auto_incremented INT. > > > I reckon the solution is going to have to be manually setting the > > field used by sqlalchemy to make the cache decision. Is there any way > > of doing this already, or am I going to have to put it in myself? > > setup the mapper() to have a composite primary key consisting of the > autoincremented integer and the UUID column. Use the primary_key > option on mapper() to achieve this. > > (also why is using a UUID "slow" ? if the column is indexed, the > difference between int/string would be miniscule compared to the fact > that you're using Python and not hardcoded C as the application > platform...) --~--~-~--~~~---~--~~ 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: Using a non-primary key as ORM identifier
On May 22, 2008, at 7:10 AM, Geoff wrote: > > Hi, > > I have split up my database horizontally and am using UUIDs to > uniquely identify a row across databases. Using UUIDs as a primary key > is slow (InnoDB) so I wanted to use the common trick of having a INT > primary key using auto_increment on each database. This is all fine, > until sqlalchemy checks its cache of objects after a query to see if > the object has already been retrieved. This breaks because the primary > key is not unique across databases when I use an auto_incremented INT. > > I reckon the solution is going to have to be manually setting the > field used by sqlalchemy to make the cache decision. Is there any way > of doing this already, or am I going to have to put it in myself? setup the mapper() to have a composite primary key consisting of the autoincremented integer and the UUID column. Use the primary_key option on mapper() to achieve this. (also why is using a UUID "slow" ? if the column is indexed, the difference between int/string would be miniscule compared to the fact that you're using Python and not hardcoded C as the application platform...) --~--~-~--~~~---~--~~ 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: BLOB/TEXT column used in key specification without a key length
since nobody is replying to this you might want to post a trac ticket.
On May 21, 2008, at 4:56 PM, Samuel wrote:
>
> Hi,
>
> I am migrating a project from SA 0.3 to SA 0.4 and the following
> field/
> index produces an error:
>
> sa.Table('resource_path', self.db_metadata,
>sa.Column('id', sa.Integer, primary_key =
> True),
>sa.Column('path', sa.Binary(255), index = True),
>mysql_engine='INNODB'
>)
>
> The complete error message is:
>
>
> Traceback (most recent call last):
> File "Guard/DBTest.py", line 43, in setUp
>self.assert_(self.db.install())
> File "/home/sam/code/spiff_guard/tests/Guard/../../src/Guard/DB.py",
> line 35, in install
>table.create(checkfirst = True)
> File "/var/lib/python-support/python2.5/sqlalchemy/schema.py", line
> 300, in create
>self.metadata.create_all(bind=bind, checkfirst=checkfirst,
> tables=[self])
> File "/var/lib/python-support/python2.5/sqlalchemy/schema.py", line
> 1215, in create_all
>bind.create(self, checkfirst=checkfirst, tables=tables)
> File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
> line 1131, in create
>self._run_visitor(self.dialect.schemagenerator, entity,
> connection=connection, **kwargs)
> File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
> line 1160, in _run_visitor
>visitorcallable(self.dialect, conn, **kwargs).traverse(element)
> File "/var/lib/python-support/python2.5/sqlalchemy/sql/visitors.py",
> line 76, in traverse
>meth(target)
> File "/var/lib/python-support/python2.5/sqlalchemy/sql/compiler.py",
> line 760, in visit_metadata
>self.traverse_single(table)
> File "/var/lib/python-support/python2.5/sqlalchemy/sql/visitors.py",
> line 30, in traverse_single
>return meth(obj, **kwargs)
> File "/var/lib/python-support/python2.5/sqlalchemy/sql/compiler.py",
> line 796, in visit_table
>self.traverse_single(index)
> File "/var/lib/python-support/python2.5/sqlalchemy/sql/visitors.py",
> line 30, in traverse_single
>return meth(obj, **kwargs)
> File "/var/lib/python-support/python2.5/sqlalchemy/sql/compiler.py",
> line 881, in visit_index
>self.execute()
> File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
> line 1760, in execute
>return self.connection.execute(self.buffer.getvalue())
> File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
> line 844, in execute
>return Connection.executors[c](self, object, multiparams, params)
> File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
> line 854, in _execute_text
>self.__execute_raw(context)
> File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
> line 916, in __execute_raw
>self._cursor_execute(context.cursor, context.statement,
> context.parameters[0], context=context)
> File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
> line 953, in _cursor_execute
>self._handle_dbapi_exception(e, statement, parameters, cursor)
> File "/var/lib/python-support/python2.5/sqlalchemy/engine/base.py",
> line 935, in _handle_dbapi_exception
>raise exceptions.DBAPIError.instance(statement, parameters, e,
> connection_invalidated=is_disconnect)
> OperationalError: (OperationalError) (1170, "BLOB/TEXT column 'path'
> used in key specification without a key length") 'CREATE INDEX
> ix_guard_resource_path_path ON guard_resource_path (path)' {}
>
>
> The same table specification works in SA 0.3. Any idea?
>
> -Samuel
> >
--~--~-~--~~~---~--~~
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: sqlalchemy decides to pull entire table
On May 21, 2008, at 7:42 PM, arashf wrote: > > I'm running this query: q = Event.query.filter(and_(Event.id < > id_under, Event.feed == True, > Event.ns_id.in_(ns_list))).limit(num).order_by([Event.updated.desc(), > Event.id.desc()]) > > For some reason, sqlalchemy decides to pull the entire table, yet, > when I don't include Event.id < id_under, it performs the correct > query. > > Here's the query that it seems to be running: SELECT event.id AS > event_id, event.ns_id AS event_ns_id, event.user_id AS event_user_id, > event.updated AS event_updated, event.type AS event_type, event.arg1 > AS event_arg1, event.arg2 AS event_arg2, event.arg3 AS event_arg3, > event.feed AS event_feed > > The summary is, supplying the id constraint seems to get rid of my > where clause. Any ideas as to what's going on here? Thanks. what happens if you say: and_(Event.idhttp://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy decides to pull entire table
On May 21, 2008, at 8:13 PM, Rick Morrison wrote: > The "and_" function is expecting two arguments, not a series of > *args. It works when you remove the third argument because you then > have the expected two arguments. and_() and or_() still take *args. --~--~-~--~~~---~--~~ 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: Using a non-primary key as ORM identifier
Have you considered using a discriminator column, an additional integer that identifies the shard and is part of a two-integer primary key? You could then use concrete polymorphic inheritance to set up mappers for both tables that would automatically set the discriminator column to the appropriate shard id for saves. I'm not familiar with the current shard support in SQLA, but it may already provide something along these lines --~--~-~--~~~---~--~~ 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: Using a non-primary key as ORM identifier
maybe somehow make it pseudo-composite using some "hardcoded" db-identifier as discriminator.. here Mike's reply from some days ago on a related question: > [sqlalchemy] Re: table without any primary_keys? > From: Michael Bayer <[EMAIL PROTECTED]> > To: sqlalchemy@googlegroups.com > Date: 2008-05-18 06:54 > > On May 17, 2008, at 9:52 PM, [EMAIL PROTECTED] wrote: > > seems such thing is disallowed, or at least Mapper complains. > > is it SQL requirement or what? > > the mapper needs some set of PK columns defined for mapped classes > so that it can identify objects. they dont need to be actual PK > columns in the database. > > in theory there just needs to be a function that can extract a > primary key from a row - it doesnt even necessarily have to be "use > these columns". though such a feature would require some API > changes. HTH svil On Thursday 22 May 2008 14:10:38 Geoff wrote: > Hi, > > I have split up my database horizontally and am using UUIDs to > uniquely identify a row across databases. Using UUIDs as a primary > key is slow (InnoDB) so I wanted to use the common trick of having > a INT primary key using auto_increment on each database. This is > all fine, until sqlalchemy checks its cache of objects after a > query to see if the object has already been retrieved. This breaks > because the primary key is not unique across databases when I use > an auto_incremented INT. > > I reckon the solution is going to have to be manually setting the > field used by sqlalchemy to make the cache decision. Is there any > way of doing this already, or am I going to have to put it in > myself? > > 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] Using a non-primary key as ORM identifier
Hi, I have split up my database horizontally and am using UUIDs to uniquely identify a row across databases. Using UUIDs as a primary key is slow (InnoDB) so I wanted to use the common trick of having a INT primary key using auto_increment on each database. This is all fine, until sqlalchemy checks its cache of objects after a query to see if the object has already been retrieved. This breaks because the primary key is not unique across databases when I use an auto_incremented INT. I reckon the solution is going to have to be manually setting the field used by sqlalchemy to make the cache decision. Is there any way of doing this already, or am I going to have to put it in myself? 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: BLOB/TEXT column used in key specification without a key length
Additional note: The same thing happens when using Index() explicitly. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
