Latest posts on sqlobject - разница в датах в select topichttps://python.su/forum/topic/596/2007-03-26T17:53:40+03:00Общий :: Базы данных :: sqlobject - разница в датах в select
2007-03-26T17:53:40+03:00j2a4223Преждевременная оптимизация – зло. Я не знаю, можно ли это сделать при помощи ORM, на чистом SQL это делается двойной выборкой из orders, что-то типа<br/><div class="code"><pre>sqlite> select * from customers;<br/>1 Рога и копыта<br/>2 Бендер и Ко<br/>3 Киса<br/>sqlite> select * from orders;<br/>1 3 10 20070326<br/>2 3 3 20070325<br/>3 3 15 20070324<br/>4 1 20 20070324<br/>5 1 7 20070324<br/>sqlite> SELECT DISTINCT c.name<br/> ...> FROM customers AS c<br/> ...> INNER JOIN orders AS o1 ON c.id = o1.customer_id<br/> ...> INNER JOIN orders AS o2 ON c.id = o2.customer_id<br/> ...> WHERE<br/> ...> o1.id < o2.id AND<br/> ...> o1.order_date - o2.order_date = 1;<br/>Киса</pre></div>
Общий :: Базы данных :: sqlobject - разница в датах в select
2007-03-26T09:25:03+03:00balu4203<blockquote><em>pythonwin</em><br/>согласен - самого напрягает - названия не от меня зависели</blockquote>Выбор sqlobject тоже ;-) ?
Общий :: Базы данных :: sqlobject - разница в датах в select
2007-03-25T14:31:30+03:00pythonwin4190<blockquote><em>j2a</em><br/>примеры как не надо называть функции/переменные.</blockquote>согласен - самого напрягает - названия не от меня зависели - за ссылку спасибо<br/><br/><blockquote><em>j2a</em><br/>В общем, сформулируй свою задачу словами.</blockquote>нужно вывести клиентов(заказчиков), которые ежедневно регистрировали заказы, если заказчик в один день разместил более одного заказа, то заказ должен учитываться только один раз, например: заказчик, который сделал все заказы в один нет не должен быть выведен :)
Общий :: Базы данных :: sqlobject - разница в датах в select
2007-03-24T06:30:39+02:00j2a4159Ух. Читай <a href="http://www.python.org/dev/peps/pep-0008">PEP8</a>. Может, конечно, есть внутренний глубокий смысл чтобы rating называть с заглавной, и order_date тоже, и сокращать amount до amt, но читабельности это не прибавляет – факт. Названия переменных/функций тоже не блещут логикой: “select9_1”, “select9”, “list1” - примеры как <em>не надо</em> называть функции/переменные.<br/><br/>И еще. Лучше бы ты словами объяснил, чего тебе надо. Потому как вместо двух действий (понять чего тебе надо, придумать решение), приходится делать три (расшифровать твой код, догадаться чего тебе нужно, придумать решение).<br/><br/>В общем, сформулируй свою задачу словами.
Общий :: Базы данных :: sqlobject - разница в датах в select
2007-03-23T22:28:12+02:00pythonwin4152всем привет!<br/>есть бд со следующей структурой:<br/><div class="code"><pre><span class="kn">from</span> <span class="nn">sqlobject</span> <span class="kn">import</span> <span class="o">*</span>
<span class="kn">from</span> <span class="nn">sqlobject.sqlbuilder</span> <span class="kn">import</span> <span class="n">EXISTS</span><span class="p">,</span> <span class="n">Select</span>
<span class="kn">from</span> <span class="nn">datetime</span> <span class="kn">import</span> <span class="n">datetime</span>
<span class="kn">import</span> <span class="nn">time</span>
<span class="k">class</span> <span class="nc">Customer</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">UnicodeCol</span><span class="p">()</span>
<span class="n">city</span> <span class="o">=</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"City"</span><span class="p">)</span>
<span class="n">Rating</span> <span class="o">=</span> <span class="n">IntCol</span><span class="p">()</span>
<span class="n">salespeople</span> <span class="o">=</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"SalesPeople"</span><span class="p">)</span>
<span class="n">orders</span> <span class="o">=</span> <span class="n">MultipleJoin</span><span class="p">(</span><span class="s">"Order1"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Order1</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span>
<span class="n">Amt</span> <span class="o">=</span> <span class="n">FloatCol</span><span class="p">()</span>
<span class="n">Odate</span> <span class="o">=</span> <span class="n">DateTimeCol</span><span class="p">()</span>
<span class="n">salespeople</span> <span class="o">=</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"SalesPeople"</span><span class="p">)</span>
<span class="n">customer</span> <span class="o">=</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"Customer"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">SalesPeople</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span>
<span class="n">name</span><span class="o">=</span><span class="n">UnicodeCol</span><span class="p">()</span>
<span class="n">comm</span> <span class="o">=</span> <span class="n">FloatCol</span><span class="p">()</span>
<span class="n">city</span> <span class="o">=</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"City"</span><span class="p">)</span>
<span class="n">customers</span> <span class="o">=</span> <span class="n">MultipleJoin</span><span class="p">(</span><span class="s">"Customer"</span><span class="p">,</span> <span class="n">joinColumn</span><span class="o">=</span><span class="s">"salespeople_id"</span><span class="p">)</span>
<span class="n">orders</span> <span class="o">=</span> <span class="n">MultipleJoin</span><span class="p">(</span><span class="s">"Order1"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">City</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">UnicodeCol</span><span class="p">()</span>
<span class="n">customers</span> <span class="o">=</span> <span class="n">MultipleJoin</span><span class="p">(</span><span class="s">"Customer"</span><span class="p">,</span> <span class="n">joinColumn</span><span class="o">=</span><span class="s">"city_id"</span><span class="p">)</span>
<span class="n">peoples</span> <span class="o">=</span> <span class="n">MultipleJoin</span><span class="p">(</span><span class="s">"SalesPeople"</span><span class="p">,</span> <span class="n">joinColumn</span><span class="o">=</span><span class="s">"city_id"</span><span class="p">)</span>
<span class="n">connection_string</span> <span class="o">=</span> <span class="s">"postgres://user:11111111@localhost:5432/test1"</span>
<span class="n">connection</span> <span class="o">=</span> <span class="n">connectionForURI</span><span class="p">(</span><span class="n">connection_string</span><span class="p">)</span>
<span class="n">sqlhub</span><span class="o">.</span><span class="n">processConnection</span> <span class="o">=</span> <span class="n">connection</span>
</pre></div><br/>подскажите, пожалуйста, как упростить ниже описанный запрос?<br/><br/><div class="code"><pre><span class="k">def</span> <span class="nf">select9_1</span><span class="p">(</span><span class="n">list1</span><span class="p">):</span>
<span class="n">prev</span><span class="o">=</span><span class="bp">None</span>
<span class="k">for</span> <span class="n">order</span> <span class="ow">in</span> <span class="n">list1</span><span class="p">:</span>
<span class="k">if</span> <span class="n">prev</span><span class="o">==</span><span class="bp">None</span><span class="p">:</span>
<span class="k">pass</span>
<span class="k">elif</span> <span class="p">(</span><span class="n">prev</span><span class="o">-</span><span class="n">time</span><span class="o">.</span><span class="n">mktime</span><span class="p">(</span><span class="n">order</span><span class="o">.</span><span class="n">Odate</span><span class="o">.</span><span class="n">timetuple</span><span class="p">()))</span><span class="o"><=</span><span class="mf">172800.0</span><span class="p">:</span>
<span class="k">return</span> <span class="bp">True</span>
<span class="n">prev</span> <span class="o">=</span> <span class="n">time</span><span class="o">.</span><span class="n">mktime</span><span class="p">(</span><span class="n">order</span><span class="o">.</span><span class="n">Odate</span><span class="o">.</span><span class="n">timetuple</span><span class="p">())</span>
<span class="k">return</span> <span class="bp">False</span>
<span class="k">def</span> <span class="nf">select9</span><span class="p">():</span>
<span class="n">k</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">for</span> <span class="n">cus</span> <span class="ow">in</span> <span class="n">Customer</span><span class="o">.</span><span class="n">select</span><span class="p">():</span>
<span class="k">if</span> <span class="n">select9_1</span><span class="p">(</span><span class="n">cus</span><span class="o">.</span><span class="n">orders</span><span class="p">):</span>
<span class="n">k</span><span class="o">+=</span><span class="mi">1</span>
<span class="k">print</span> <span class="n">cus</span><span class="o">.</span><span class="n">name</span>
<span class="k">print</span> <span class="s">"k = "</span><span class="p">,</span> <span class="n">k</span>
</pre></div>хорошо бы вообще написать в одну строку или даже одним запросом