[sqlalchemy] Re: How to use "distinct" in a class based query
OK, after 2 days of trying and searching creating a post in the google groups leads to rephrasing the problem. And this often leads to finding the solution: I managed to do it this way: MyTable.query.with_entities(MyTable.columnB).distinct().all() Problem solved... Am Sonntag, 8. Oktober 2017 09:27:49 UTC+2 schrieb Cornelius Kölbel: > > Hi, > > I have a table with many columns (columnA, columnB, columnC) and I want to > know, how many different entries in columnB are. > I would do such an SQL query: > > select distinct columnB from myTable; > > I see there is also ``distinct()`` in SQL Alchemy. > > However - I am using Class defined ORM like this: > > class MyTable(db.Model): > __tablename__ = "myTable" > columnA = db.Column(...) > columnB = db.Column(...) > columnC = db.Column(...) > > Thus, I am querying like this: > > > MyTable.query.all() > > The problem seems to be that I was not able to figure out a way to reduce > the columns, e.g. when I add a ``distinct`` > > > MyTable.query.distinct.all() > > The distinct function will always use **all** columns. > I also tried ``load_only`` with no success. > > I understand, I could define a class, containing only the required column, > then I am told, I should just some ``extend_existing``. > > What is the required way to use ``distinct`` in a class based query? > > Thanks a lot > Cornelius > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How to use "distinct" in a class based query
Hi, I have a table with many columns (columnA, columnB, columnC) and I want to know, how many different entries in columnB are. I would do such an SQL query: select distinct columnB from myTable; I see there is also ``distinct()`` in SQL Alchemy. However - I am using Class defined ORM like this: class MyTable(db.Model): __tablename__ = "myTable" columnA = db.Column(...) columnB = db.Column(...) columnC = db.Column(...) Thus, I am querying like this: MyTable.query.all() The problem seems to be that I was not able to figure out a way to reduce the columns, e.g. when I add a ``distinct`` MyTable.query.distinct.all() The distinct function will always use **all** columns. I also tried ``load_only`` with no success. I understand, I could define a class, containing only the required column, then I am told, I should just some ``extend_existing``. What is the required way to use ``distinct`` in a class based query? Thanks a lot Cornelius -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] DB sync on application level
Hello Jonathan, thanks a lot for your comment and the hint with the two-phase commits. Kind regards Cornelius Am Sonntag, den 27.09.2015, 15:42 -0700 schrieb Jonathan Vanasco: > I don't like this idea. > > but... > > You should familiarize yourself with two-phase commits. sqlalchemy > supports this on mysql and postgresql. basically everyone votes to > commit yay/nay in phase 1, then a commit is made (if unanimous yays) > or rollback executed in phase 2. > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/1kdv2pWCcGQ/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- Cornelius Kölbel cornelius.koel...@netknights.it +49 151 2960 1417 NetKnights GmbH http://www.netknights.it Landgraf-Karl-Str. 19, 34131 Kassel, Germany Tel: +49 561 3166797, Fax: +49 561 3166798 Amtsgericht Kassel, HRB 16405 Geschäftsführer: Cornelius Kölbel -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. signature.asc Description: This is a digitally signed message part
[sqlalchemy] What about bit fields / bit masks?
Hello, this is maybe more about the database design. I want to store several boolean states of an object. The object may also have more than one state and the available possible states may increase in future. So I want to avoid adding BOOL columns for every new state and I though, hey, what about bit fields - one column, that can store many boolean information/flags. I understand that MySQL provides a datatype BIT, but which may lead to problems, depending on the version and table type. What about sqlalchemy? Is there a reasonable way to use bit masks? Would you recommend anyway to avoid such a design, since it is not that what SQL originally was designed for? I also want the program to be able to run on mysql or postgres or whatever. So maybe choosing bit masks is a bad idea anyway, since it might lead to problems with different database backends? Thanks a lot for your thoughts Cornelius -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. signature.asc Description: This is a digitally signed message part
[sqlalchemy] subquery? outer join with 3 tables
Hi there,
I am wondering how the following would be translated to sqlalchemy.
I have three tables:
ClientMachine
MachineToken
Token
The table MachineToken acts as n:m mapping between ClientMachine and
Token.
Each machine can have several tokens assigned.
Looks like this:
machinetoken_table = sa.Table('MachineToken', meta.metadata,
sa.Column('id',
sa.types.Integer(),
sa.Sequence('machinetoken_seq_id', optional=True),
primary_key=True, nullable=False),
sa.Column(token_id,
sa.types.Integer(), ForeignKey('Token.privacyIDEATokenId')),
sa.Column(machine_id, sa.types.Integer(), ForeignKey('ClientMachine.id')),
sa.Column(application, sa.types.Unicode(64)),
UniqueConstraint('token_id', 'machine_id', 'application', name='uix_1'),
implicit_returning=implicit_returning,
)
Now I'd like to get a list of all machines and if the machine has token-
and application-information also this information.
I figured out a left outer join:
select cl.cm_name
, mt.application
, (select privacyIDEATokenSerialnumber from Token where
privacyIDEATokenId = mt.token_id) as Serial
FROM ClientMachine cl
LEFT JOIN MachineToken mt
ON cl.id = mt.machine_id
This will give me one machine entry per assigned Token or
MachineToken.application. This is my intended result. fine.
I need to translate the MachineToken.token_id to a human readable
Serialnumber, this is why I have the line
(select privacyIDEATokenSerialnumber from Token where
privacyIDEATokenId = mt.token_id)
To my understandung I would do in SQLA something like this:
Session.query(ClientMachine).outerjoin(MachineToken).filter(ClienteMachine.id
== MachineToken.machine_id)
But how would I add the Serialnumber from the table Token?
Thanks a lot and kind regards
Cornelius
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] subquery? outer join with 3 tables
Am 08.07.2014 18:51, schrieb Jonathan Rogers:
On 07/08/2014 12:39 PM, Cornelius Kölbel wrote:
Hi there,
I am wondering how the following would be translated to sqlalchemy.
I have three tables:
ClientMachine
MachineToken
Token
The table MachineToken acts as n:m mapping between ClientMachine and
Token.
Each machine can have several tokens assigned.
Looks like this:
machinetoken_table = sa.Table('MachineToken', meta.metadata,
sa.Column('id',
sa.types.Integer(),
sa.Sequence('machinetoken_seq_id', optional=True),
primary_key=True, nullable=False),
sa.Column(token_id,
sa.types.Integer(), ForeignKey('Token.privacyIDEATokenId')),
sa.Column(machine_id, sa.types.Integer(), ForeignKey('ClientMachine.id')),
sa.Column(application, sa.types.Unicode(64)),
UniqueConstraint('token_id', 'machine_id', 'application', name='uix_1'),
implicit_returning=implicit_returning,
)
Now I'd like to get a list of all machines and if the machine has token-
and application-information also this information.
I figured out a left outer join:
select cl.cm_name
, mt.application
, (select privacyIDEATokenSerialnumber from Token where
privacyIDEATokenId = mt.token_id) as Serial
FROM ClientMachine cl
LEFT JOIN MachineToken mt
ON cl.id = mt.machine_id
I'd just use another outer join rather than a subquery:
select cl.cm_name
, mt.application
, privacyIDEATokenSerialnumber
FROM ClientMachine cl
LEFT JOIN MachineToken mt
ON cl.id = mt.machine_id
LEFT JOIN Token
ON privacyIDEATokenId = mt.token_id
This will give me one machine entry per assigned Token or
MachineToken.application. This is my intended result. fine.
I need to translate the MachineToken.token_id to a human readable
Serialnumber, this is why I have the line
(select privacyIDEATokenSerialnumber from Token where
privacyIDEATokenId = mt.token_id)
To my understandung I would do in SQLA something like this:
Session.query(ClientMachine).outerjoin(MachineToken).filter(ClienteMachine.id
== MachineToken.machine_id)
But how would I add the Serialnumber from the table Token?
Since you've already defined the foreign keys in SQLA, you shouldn't
need to do so again in the query. You can get any number of values
from a query. Try something like this:
Session.query(ClientMachine, Token.privacyIDEATokenSerialnumber) \
.outerjoin(MachineToken).outerjoin(Token)
That should give pairs of a ClientMachine instance and a
privacyIDEATokenSerialnumber.
Hi Jonathon,
thanks a lot for the hint. Looks good to me.
Kind regards
Cornelius
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] flask sql cann't insert Variable in VALUES
You should take a look into your webservers error log.
Kind regards
Cornelius
Am 04.07.2014 12:19, schrieb 'Frank Liou' via sqlalchemy:
I try to insert username in to my table
it show
Internal Server Error
The server encountered an internal error and was unable to complete
your request. Either the server is overloaded or there is an error in
the application.
it maybe mean no request
i try to change username to '123123'
then it works
what's problem with this?
@app.route('/user/username',methods=['GET','POST'])
def hello(username):
if request.method=='POST':
save_friends(username)
return username
def save_friends(username):
conn = engine.connect()
conn.execute(INSERT INTO friends(name) VALUES(username))
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
signature.asc
Description: OpenPGP digital signature
Re: [sqlalchemy] getting error with column name end using geoalchemy2
I once had a very strange error with a table called audit on an oracle
database.
It turned out, that exspecially on oracle audit was a reserved word -
while it worked out fine on any other database.
I would recommend trying to use other column names.
As end is surrounded by double quites your database postgres also know
it as a reserved word. (see
https://stackoverflow.com/questions/5570783/using-end-as-column-name-in-ruby-on-rails-mysql)
Change the column name.
Kind regards
Cornelius
Am 21.06.2014 14:34, schrieb Chung WONG:
Hi list,
I am encountering a very strange error and I am scratching my head and
got no idea what is going on.
class Line(Base):
__tablename__ = 'lines'
id = Column(Integer, Sequence('line_id_seq'), primary_key=True)
start = Column(Geometry('POINT'), nullable=False, *index=False*)
*end* = Column(Geometry('POINT'), nullable=False, *index=False*)
On creating this table, it threw a strange error:
/sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at
or near end/
/LINE 1: ...INDEX idx_lines_end ON public.lines USING GIST (end)/
/ ^/
/ 'CREATE INDEX idx_lines_end ON public.lines USING GIST (end)' {}/
The created table is :
CREATE TABLE lines
(
id integer NOT NULL,
start geometry(Point) NOT NULL,
*end* geometry(Point) NOT NULL,
CONSTRAINT lines_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE lines
OWNER TO postgres;
CREATE INDEX idx_lines_start
ON lines
USING gist
(start);
It is weird there are quotes surrounding the word *end* , and although
I have specified *index=False*, for some reason indexs are still
created automatically.
Anything other than *end*, such as *end_, end1 *worked perfectly.
Is end a keyword for *postgis* or *geoalchemy2*?
Thanks
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] filter seams not to work with 0.8.0
Hi list,
I have problems when upgrading to SQLAlchemy 0.8.0.
I have code like this:
audit_q =
self.session.query(AuditTable).filter(AuditTable.serial.like('something')).order_by(order_dir)
which was running great with mysql on sqlalchemy 0.7.4
With 0.8.0 it seams that I never get results when using a filter.
Neither a .like filter nor a condition like AuditTable.serial== works
Any idea on this?
Thanks a lot and kind regards
Cornelius
signature.asc
Description: OpenPGP digital signature
Re: [sqlalchemy] filter seams not to work with 0.8.0
Am 20.03.2013 15:11, schrieb Cornelius Kölbel:
Hi list,
I have problems when upgrading to SQLAlchemy 0.8.0.
I have code like this:
audit_q =
self.session.query(AuditTable).filter(AuditTable.serial.like('something')).order_by(order_dir)
which was running great with mysql on sqlalchemy 0.7.4
With 0.8.0 it seams that I never get results when using a filter.
Neither a .like filter nor a condition like AuditTable.serial== works
Any idea on this?
Thanks a lot and kind regards
Cornelius
Hi,
my confusion is perfect!
After doing some code cleanup it works as expected.
I have to take a look at the diff to find out, why now...
Kind regards
Cornelius
signature.asc
Description: OpenPGP digital signature
[sqlalchemy] unicode in where clause on mysql
Hi there, I am trying to do a select on a table, where a user has a /username/ with a German umlaut like kölbel. The table in the mysql-database is utf-8. I also failed when the table was latin1. My problem is that by no(!) chance I manage to match the user and get a row from the select statement. (see version2) Should it be possible at all or am I just screwing up with my non-existent unicode-skills! ;-) Thanks a lot and kind regards Cornelius # -*- coding: utf-8 -*- from sqlalchemy import create_engine from sqlalchemy import MetaData from sqlalchemy.orm import sessionmaker from sqlalchemy import Table CONNECT_STRING=mysql://my_connect_string TABLE = linotp_user USER = ukölbel engine = create_engine(CONNECT_STRING, echo=False) meta= MetaData() Session = sessionmaker(bind=engine) session = Session() table = Table(TABLE, meta, autoload=True, autoload_with=engine) print type of user: , type(USER) select = table.select(uusername = '%s' % USER) print select statement: , select print type of select: , type(select) print Printing rows, version 1 rows = session.execute(select) for row in rows: print ::: , row print Printing rows, version 2 sel_string=uselect * from %s where username = '%s' % (TABLE, USER) print type(sel_string) print sel_string rows = engine.execute(sel_string) for row in rows: print ::: , row session.close() signature.asc Description: OpenPGP digital signature
Re: [sqlalchemy] Re: SA and IBM DB2
Hello,
one year gone and I wonder if there might be any news on the question of
sqlalchemy and DB2.
The previous patches of ibm_db_sa
(https://bitbucket.org/jazle/ibm_db_sa/downloads) do not exist anymore.
But the current ibm_db_sa 0.1.6 produces an import error at
from sqlalchemy import logging
(at least at the 0.1.6 version from pypi)
So what would be the current status of sqlalchemy and DB2?
Thanks a lot and kind regards
Cornelius
Am 06.07.2011 17:36, schrieb Michael Bayer:
On Jul 6, 2011, at 11:19 AM, Christian Klinger wrote:
Hi Michael,
i am intrested in writing a dialect for DB2. Is there any howto which covers
what is needed to start. Do you think we should write an extension, or
should this dialect in sqlalchemy itself?
first off, HOORAY, secondly, this would be a dialect within SQLAlchemy itself
under sqlalchemy.dialects.
Here are the two files we would need:
sqlalchemy/dialects/db2/base.py
sqlalchemy/dialects/db2/ibm_db.py
So in base.py, the base dialect classes, things that deal with the kind of
SQL that DB2 deals with.Preferably no details that are specific to the
DBAPI. In ibm_db.py is where things that are specific to IBMs DBAPI are
present.At some later point, if for example pyodbc could also connect to
DB2, we'd add a pyodbc.py file there.
Then to do what's in base.py, ibm_db.py, you need to emulate what's in all
the other dialects. Some smaller ones to look at are firebird, sybase.
More involved are mssql, postgresql, oracle. The MySQL dialect is good too
but that one is particularly complicated due to a lot of difficulties MySQL
presents.
When I write a new dialect from scratch, the first thing I do is just to get
it to run at all, which usually means a script like this:
e = create_engine('db2:ibm_db://scott:tiger@localhost/test')
c = e.connect()
print c.execute('SELECT 1').fetchall()
That's pretty much hello world. You might try to work with a few variants
of hello world just to get things going.
Then, you can start moving onto the actual tests. This is also an
incremental process, and I usually start with test/sql/test_query.py which
tests basic round trips.The last section of README.unittests has several
paragraphs on how to test new dialects and includes an overview of which
tests to start with.
Thanks in advance
Christian
On Jun 29, 2011, at 6:43 AM, Luca Lesinigo wrote:
Hello there. I'd like to use SQLalchemy with an existing db2 database
(I can already access it with plain SQL using pyODBC from a python-2.6/
win32 system).
Googling around, I found http://code.google.com/p/ibm-db and it seems
to have an updated DB-API driver for python-2.6/win32, but the latest
SA adapter is for sqlalchemy-0.4.
Is there any way to access DB2 from sqlalchemy-0.6 or -0.7?
If that helps, I'm gonna use it in read-only (ie, no INSERT, UPDATE,
DELETE queries will be issued nor would they be accepted by the db)
A project I'd like to take on at some point, or to get someone else to do
it, would be to write a modernized SQLAlchemy 0.7 dialect for DB2, where we
would use DB2's DBAPI, but not their SQLAlchemy dialect which is out of
date and they appear to not be doing much with. I'd write a new dialect
rather than porting/looking at the one IBM wrote just so there's no
potential licensing issues. The new DB2 dialect would live with all the
other dialects under the SQLAlchemy project itself.
I understand DB2 has a free express edition so it would be a matter of
getting that going and working out the dialect. Dialects aren't too
hard to write so we do get them contributed, but for the moment we don't
have a DB2 story for modern SQLAlchemy versions.
thanks, Luca
--
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.
signature.asc
Description: OpenPGP digital signature
[sqlalchemy] reserved words in oracle
Hi, I encountered another RESERVED_WORD in oracle: audit As this seems to be a simple patch in sqlalchemy/dialects/oracle/base.py I am just dropping this note. (http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_keywd.htm) Kind regards Cornelius signature.asc Description: OpenPGP digital signature
[sqlalchemy] option is a keyword in oracle
Dear list, I defined a table with a column, that is called option. I got a redhat 5 system with sqlalchemy 0.5.5. When I try to do a paster setup-app the machine tries to create the table which contains columns default and option, but it breaks with an ORA error invalid identifier. It seems like this: option is a keyword in Oracle, and it does not get enclosed in quotes. I see that the other column columnd is enclosed in quotes. Obviously sqlalchemy knows the keyword default and puts in in quotes. But it does not seem to know the keyword option and does not put it in quotes. I think in newer versions this was fixed. Do you know in which version of sqlalchemy started to see option as keyword? Kind regards Cornelius signature.asc Description: OpenPGP digital signature
Re: [sqlalchemy] option is a keyword in oracle
Am 26.04.2012 23:22, schrieb Michael Bayer: On Apr 26, 2012, at 5:11 PM, Cornelius Kölbel wrote: Hello Michael, thanks for the answer, thanks for sqlalchemy and thanks for the link. Finally indeed we installed all packages via pip and at least the option problem was gone. Unfortunately we experience another problem. Our model specification contains several sqlalchemy.types.UnicodeText() columns. They worked fine under mysql, postgres and sqlite, but we are experiencing the (logical) problem with oracle. (Now it is getting hard, since I am not the oracle expert). So the tables get created with the columns -- specified as UnicodeText in the model -- as CLOB/NCLOB on oracle. Ok, that's the way it is described here http://docs.sqlalchemy.org/en/latest/core/types.html#sql-standard-types. But this leads to an uncomfortable behaviour: since the behaviour with a mysql or postgres backend will be different to that of the oracle backend. While our application is able to search, sort and filter (where) those columns in mysql and postgres it will fail with oracle as a backend, since in oracle NCLOBs can not be searched, filtered or sorted, and we get the error message: inconsistent datatypes: expected - got NCLOB. What I am aiming at is: an application with sqlalchemy will behave differently if the backend is either mysql (will work) or oracle (will break). Should the mapping of the unicodetext to the column data type have same effects on different databases? Bad thing: to my understanding oracle provides no unlimited searchable string/character data type. SQLA can only approach backend-agnostic behavior to a certain degree. The behavior of unlimited-size text fields stored using LOB methodologies is one area where this behavior can't really continue; there are great behavioral differences in these datatypes across many backends, not just Oracle. in SQL we generally don't try comparing or sorting unlimited-size BLOB, CLOB columns. Even on databases which support this, it is vastly inefficient as you can't really index unlimited-size columns reasonably. If you have columns which you need to use in filter criterion or sorting, they should be VARCHAR on all backends. PG supports VARCHAR of unlimited size, Oracle up to 4000 characters. Not sure about MySQL, but in any case columns that are used in comparisons should be of limited size. If you're doing full text searching, you should be using text searching extensions for that - Oracle should have something available in this area as well (according to this: http://www.oracle.com/pls/db111/portal.portal_db?frame=selected=7 it was introduced in Oracle 11g). To do text searching in an agnostic way with SQLAlchemy can be achieved with custom function and type constructs, once you have decided what the equivalent operations will be across all the target backends. We probably would have been better off with using Unicode(4000) or something like that. We will do some thinking. Thanks a and kind regards Cornelius signature.asc Description: OpenPGP digital signature
Re: [sqlalchemy] change table contents with alembic
Hello again,
ok, it could work this way:
import sqlalchemy as sa
from alembic import context
config = context.config
engine = sa.engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.')
engine.echo = False
metadata = sa.MetaData(engine)
token = sa.Table('Token', metadata, autoload=True)
upd =
token.update(values={token.c.Count:token.c.Count+1}).where(token.c.TokenId
200)
engine.execute(upd)
...but I wonder, if there would be less lines of code...
Kind regards
Cornelius
Am 01.04.2012 08:48, schrieb Cornelius Koelbel:
Hi,
yesterday i stumbled upon alembic to upgrade the database schema. It
looks very promising and I am evaluating what is possible with what kind
of effort.
What would be the easiest way to update table contents within alembic
like this:
update test set name='Fred' where name='fred'
...or one day even more complex! ;-)
Kind regards
Cornelius
signature.asc
Description: OpenPGP digital signature
[sqlalchemy] update table row immediately after writing
Hello, I am using sqlalchemy with pylons. I write audit log messages to table. I use the orm to map my table to my class. orm.mapper(AuditTable, audit_table) self.engine = create_engine(connect_string) metadata.bind = self.engine metadata.create_all() self.sm = orm.sessionmaker(bind=self.engine, autoflush=True, autocommit=False, expire_on_commit=True) self.session = orm.scoped_session(self.sm) In fact I create the entry with the to be logged information at = AuditTable( info=asdasd, admin=MisterX ... ) Then I add it to my session... self.session.add(at) self.session.flush() self.session.commit() Now I'd like to calculate a signature for each log entry. The primary key id should also be included in the signature. But the id is only available after self.session.flush. So obviously I need to do an update on the table, to rewrite the signature to this table entry. What would be the easiest way to do this? Kind regards Cornelius signature.asc Description: OpenPGP digital signature
Re: [sqlalchemy] update table row immediately after writing
OK, after some more reading and thinking, I think i managed it this way: self.session.add(at) self.session.flush() # At this point at contains the primary key id at.signature = self._sign( at ) self.session.merge(at) self.session.commit() Kind regards Cornelius Am 30.05.2011 15:29, schrieb Cornelius Kölbel: Hello, I am using sqlalchemy with pylons. I write audit log messages to table. I use the orm to map my table to my class. orm.mapper(AuditTable, audit_table) self.engine = create_engine(connect_string) metadata.bind = self.engine metadata.create_all() self.sm = orm.sessionmaker(bind=self.engine, autoflush=True, autocommit=False, expire_on_commit=True) self.session = orm.scoped_session(self.sm) In fact I create the entry with the to be logged information at = AuditTable( info=asdasd, admin=MisterX ... ) Then I add it to my session... self.session.add(at) self.session.flush() self.session.commit() Now I'd like to calculate a signature for each log entry. The primary key id should also be included in the signature. But the id is only available after self.session.flush. So obviously I need to do an update on the table, to rewrite the signature to this table entry. What would be the easiest way to do this? Kind regards Cornelius signature.asc Description: OpenPGP digital signature
Re: [sqlalchemy] No attribute of child relation contained in parent object
Hi List,
ok, i figured it out. My understanding of the documentation was a bit
different.
I really worked out this way. But
Thekey in the properties is the attribute name, that will be added to
the parent object.
So doing this
orm.mapper(User, user_table, properties={
'realms':relation(TokenRealm, backref=backref('user')
)
...will add an attribute User.realms.
Kind regards
Cornelius
Am 26.01.2011 11:45, schrieb cornelinux:
Hi,
I am just starting to use the relation between two tables.
The problem started with the child data not being written to the child
tables.
I played around, and now I am totally confused.
Maybe someone can shed some light on this.
I got these tables:
{{{
user_table = sa.Table('User', meta.metadata,
sa.Column('UserId', sa.types.Integer(),
primary_key=True),
sa.Column('UserDesc',sa.types.UnicodeText(),
default=u''),
)
userrealm_table = sa.Table('UserRealm', meta.metadata,
sa.Column('id', sa.types.Integer(), primary_key=True),
sa.Column('user_id',
sa.types.Integer(),ForeignKey('User.UserId')),
sa.Column('realm_id', sa.types.Integer(),
nullable=False )
)
}}}
I got the following classes:
{{{
class User(object):
def __init__(self, desc):
log.debug(' __init__(%s)' % desc)
self.UserDesc = serial
classe UserRealm(object):
def __init__(self, realm):
log.debug(setting realm_id to %i % realm)
self.realm_id = realm
}}}
A user may belong to several realms. All the relation stuff should be
done in the mappers:
{{{
orm.mapper(UserRealm, userrealm_table)
orm.mapper(User, user_table, properties={
'children':relationship(UserRealm,backref='user', cascade=save-
update)
})
}}}
Now I am at the point, that the User object contains no attribute
identifying the realm...
I thought this attribute should be generated by the relation
definition?
Any ideas on this?
Kind regards
Cornelius
signature.asc
Description: OpenPGP digital signature
