Thread: [SQLObject] select only specified columns
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
sqlobject-discuss
|
From: Imre H. <ble...@gm...> - 2010-06-22 09:20:49
|
Hi everyone! Is it possible to select only a few columns from a table? My primary problem is speed when i select all rows from a big table (>1 mill. record). But i only need 2-3 columns from it. I think it's because all other columns & related objects are selected. FYI: The exact scenario is: Customers table: id, name, lots of other data. For a master/detail form, i need to fill the list with id and name only. When the user selects a row in the list, i get the customer object via id, and fill the details form. Thanks for advance: Imre Horvath |
|
From: Oleg B. <ph...@ph...> - 2010-06-22 09:43:02
|
On Tue, Jun 22, 2010 at 11:20:39AM +0200, Imre Horvath wrote:
> Is it possible to select only a few columns from a table?
No, but you can lazily selects columns using lazyColumns=True; with this
SQLObject doesn't initially draw any column, only id's; but when you touch a
row and ask for a column SQLObject executes a specific one-row one-column
SELECT query. If you are going to touch a million rows this lead to at least
few millions queries; perhaps it'd be faster to select all columns at once,
but YMMV.
Oleg.
--
Oleg Broytman http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Imre H. <ble...@gm...> - 2010-06-22 10:09:31
|
Thank you, that's what I've looking for. In my case, I only fill details view for one record only, 30-40 columns, it's fast enough with one-column queries too. But I wonder if it's possible to override get method to temporary switch off lazyColumns? I mean: class Customer(SQLObject): def get(self, id): self.lazyColumns = False entity = Customer.get(id) self.lazyColumns = True return entity Imre 2010. 06. 22, kedd keltezéssel 13.42-kor Oleg Broytman ezt írta: > On Tue, Jun 22, 2010 at 11:20:39AM +0200, Imre Horvath wrote: > > Is it possible to select only a few columns from a table? > > No, but you can lazily selects columns using lazyColumns=True; with this > SQLObject doesn't initially draw any column, only id's; but when you touch a > row and ask for a column SQLObject executes a specific one-row one-column > SELECT query. If you are going to touch a million rows this lead to at least > few millions queries; perhaps it'd be faster to select all columns at once, > but YMMV. > > Oleg. |
|
From: Oleg B. <ph...@ph...> - 2010-06-22 10:21:37
|
On Tue, Jun 22, 2010 at 12:09:22PM +0200, Imre Horvath wrote:
> Thank you, that's what I've looking for.
> In my case, I only fill details view for one record only, 30-40 columns,
> it's fast enough with one-column queries too.
Oops, I've forgotten - lazyColumns by itself prevents loading of columns
but when you touch a row SQLObject will load all columns for the row. To
load only one column also set
class sqlmeta:
cacheValues = False
> But I wonder if it's possible to override get method to temporary switch
> off lazyColumns?
No. lazyColumns is taken into account when constructing the row, so it's
meaningless to set it afterwards.
Oleg.
--
Oleg Broytman http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Mark <mar...@gm...> - 2010-07-01 15:50:23
|
Even if you weren't using SQLObject I believe it would make more sense to create a separate name/id table and then only select from the 40 column table when you know what row you want. Mark |
|
From: Oleg B. <ph...@ph...> - 2010-07-01 16:28:38
|
On Thu, Jul 01, 2010 at 03:48:38PM +0000, Mark wrote:
> Even if you weren't using SQLObject I believe it would make more
> sense to create a separate name/id table and then only select
> from the 40 column table when you know what row you want.
Or use a VIEW.
Oleg.
--
Oleg Broytman http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Petr J. <pet...@tp...> - 2010-07-01 18:14:38
|
+1 VIEW is, IMHO, en excelent way to go, when the SQLObject is close to its limits. Petr On 1 July 2010 18:28, Oleg Broytman <ph...@ph...> wrote: > On Thu, Jul 01, 2010 at 03:48:38PM +0000, Mark wrote: > > Even if you weren't using SQLObject I believe it would make more > > sense to create a separate name/id table and then only select > > from the 40 column table when you know what row you want. > > Or use a VIEW. > > Oleg. > -- > Oleg Broytman http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > > ------------------------------------------------------------------------------ > This SF.net email is sponsored by Sprint > What will you do first with EVO, the first 4G phone? > Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
|
From: Mark <mar...@gm...> - 2010-07-01 18:39:24
|
> +1VIEW is, IMHO, en excelent way to go, when the SQLObject is close to its > limits.Petr How do you create and use a view in SQLObject? And is not a view a tradeoff on response time vs DB size? If I create a separate table with a million rows I may add 50mb to the DB size, but if I use a view the DB has to select out the view each time and that 50mb may not cache. Mark |
|
From: Petr J. <pet...@tp...> - 2010-07-01 18:52:46
|
Creat View in the SQL and than use it in the SQLObject as an ordinary table. You can handle some more complex SQL SELECT within the view. Of course it is read only (I am on Firebird - maybe some other databases behave differently) HTH Petr On 1 July 2010 20:38, Mark <mar...@gm...> wrote: > > > +1VIEW is, IMHO, en excelent way to go, when the SQLObject is close to > its > > limits.Petr > > How do you create and use a view in SQLObject? > > And is not a view a tradeoff on response time vs DB size? If I create a > separate > table with a million rows I may add 50mb to the DB size, but if I use a > view the > DB has to select out the view each time and that 50mb may not cache. > > Mark > > > > > ------------------------------------------------------------------------------ > This SF.net email is sponsored by Sprint > What will you do first with EVO, the first 4G phone? > Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
|
From: Oleg B. <ph...@ph...> - 2010-07-01 19:32:07
|
On Thu, Jul 01, 2010 at 08:52:38PM +0200, Petr Jake?? wrote: > Creat View in the SQL and than use it in the SQLObject as an ordinary table. > You can handle some more complex SQL SELECT within the view. +1 > Of course it is read only (I am on Firebird - maybe some other databases > behave differently) Yes, there are VIEWs that support updating. Another way is to use ViewSQLObject. See http://sqlobject.org/Views.html Oleg. -- Oleg Broytman http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X