The [documentation on counting][1] says that for `group_by` queries it is better to use `func.count()`:

    from sqlalchemy import func
    session.query(Table.column, func.count(Table.column)).group_by(Table.column).all()


  [1]: http://www.sqlalchemy.org/docs/orm/tutorial.html#counting

If you are using `Table.query` property:

    from sqlalchemy import func
    Table.query.with_entities(Table.column, func.count(Table.column)).group_by(Table.column).all()

If you are using `session.query()` method (as stated in miniwark&#39;s answer):

    from sqlalchemy import func
    session.query(Table.column, func.count(Table.column)).group_by(Table.column).all()


You can also count on multiple groups and their intersection:

    self.session.query(func.count(Table.column1),Table.column1, Table.column2).group_by(Table.column1, Table.column2).all()

The query above will return counts for all possible combinations of values from both columns.

Will this validate in XHTML?

    &lt;span&gt;hello&lt;span&gt;world&lt;/span&gt;&lt;/span&gt;

Are nested span tags OK in XHTML?

Does anyone know if there is a way I can insert values into a C# Dictionary when I create it? I can, but don&#39;t want to, do 
`dict.Add(int, &quot;string&quot;)` for each item if there is something more efficient like: 

    Dictionary&lt;int, string&gt;(){(0, &quot;string&quot;),(1,&quot;string2&quot;),(2,&quot;string3&quot;)};

How to insert values into C# Dictionary on instantiation?

I want a &quot;group by and count&quot; command in sqlalchemy. How can I do this?

Group by &amp; count function in sqlalchemy

<p>I want a "group by and count" command in sqlalchemy. How can I do this?</p>


[NumPy][1] is an extremely useful library, and from using it I&#39;ve found that it&#39;s capable of handling matrices which are quite large (10000 x 10000) easily, but begins to struggle with anything much larger (trying to create a matrix of 50000 x 50000 fails). Obviously, this is because of the massive memory requirements.

Is there is a way to create huge matrices natively in NumPy (say 1 million by 1 million) in some way (without having several terrabytes of RAM)?

  [1]: http://en.wikipedia.org/wiki/NumPy


Very large matrices using Python and NumPy

I want to print the bit representation of numbers onto console, so that I can see all operations that are being done on bits itself.

How can I possibly do it in python?

printing bit representation of numbers in python

I want to inherit from a class in a file that lies in a directory above the current one.

Is it possible to relatively import that file?

How to import a Python class that is in a directory above?

I have a function that take an argument which can be either a single item or a double item:

    def iterable(arg)
        if #arg is an iterable:
            print &quot;yes&quot;
        else:
            print &quot;no&quot;

so that:
&lt;pre&gt;
&gt;&gt;&gt; iterable( (&quot;f&quot;,&quot;f&quot;) )
yes

&gt;&gt;&gt; iterable( [&quot;f&quot;,&quot;f&quot;] )
yes

&gt;&gt;&gt; iterable(&quot;ff&quot;)
no
&lt;/pre&gt;
The problem is that string is technically iterable, so I can&#39;t just catch the ValueError when trying `arg[1]`. I don&#39;t want to use isinstance(), because that&#39;s not good practice (or so I&#39;m told).

how to tell a variable is iterable but not a string

I want to extend a large C project with some new functionality, but I really want to write it in Python.  Basically, I want to call Python code from C code.  However, Python-&gt;C wrappers like SWIG allow for the OPPOSITE, that is writing C modules and calling C from Python.

I&#39;m considering an approach involving IPC or RPC (I don&#39;t mind having multiple processes); that is, having my pure-Python component run in a separate process (on the same machine) and having my C project communicate with it by writing/reading from a socket (or unix pipe).  my python component can read/write to socket to communicate.  Is that a reasonable approach?  Is there something better?  Like some special RPC mechanism?

Thanks for the answer so far - **however, i&#39;d like to focus on IPC-based approaches since I want to have my Python program in a separate process as my C program.  I don&#39;t want to embed a Python interpreter.  Thanks!**

How do you call Python code from C code?

I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender.  My query looks like this:

    SELECT 
        `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails` 
    GROUP BY LOWER(`fromEmail`) 
    ORDER BY `timestamp` DESC

The query almost works as I want it — it selects records grouped by e-mail. The problem is that the subject and timestamp don&#39;t correspond to the most recent record for a particular e-mail address.

For example, it might return:

    fromEmail: john@example.com, subject: hello
    fromEmail: mark@example.com, subject: welcome

When the records in the database are:

    fromEmail: john@example.com, subject: hello
    fromEmail: john@example.com, subject: programming question
    fromEmail: mark@example.com, subject: welcome

If the &quot;programming question&quot; subject is the most recent, how can I get MySQL to select that record when grouping the e-mails?

MySQL &quot;Group By&quot; and &quot;Order By&quot;

I have a column that looks something like this:

    CASE
        WHEN col1 &gt; col2 THEN SUM(col3*col4)
        ELSE 0
    END AS some_product

And I would like to put it in my GROUP BY clause, but this seems to cause problems because there is an aggregate function in column.  Is there a way to GROUP BY a column alias such as `some_product` in this case, or do I need to put this in a subquery and group on that?

SQL GROUP BY CASE statement with aggregate function

There is a table `messages` that contains data as shown below:

    Id   Name   Other_Columns
    -------------------------
    1    A       A_data_1
    2    A       A_data_2
    3    A       A_data_3
    4    B       B_data_1
    5    B       B_data_2
    6    C       C_data_1

If I run a query `select * from messages group by name`, I will get the result as:

    1    A       A_data_1
    4    B       B_data_1
    6    C       C_data_1

What query will return the following result?

    3    A       A_data_3
    5    B       B_data_2
    6    C       C_data_1

That is, the last record in each group should be returned.

At present, this is the query that I use:

    SELECT
      *
    FROM (SELECT
      *
    FROM messages
    ORDER BY id DESC) AS x
    GROUP BY name

But this looks highly inefficient. Any other ways to achieve the same result?


Retrieving the last record in each group - MySQL

Consider


     create table pairs ( number a, number b ) 


Where the data is 
    

    1,1
    1,1
    1,1
    2,4
    2,4
    3,2
    3,2
    5,1




Etc. 

What query gives me the distinct values the number column b has So I can see

    1,1
    5,1
    2,4
    3,2

only 

I&#39;ve tried


    select distinct ( a ) , b from pairs group by b 

but gives me &quot;not a group by expression&quot; 


   

Distinct pair of values SQL

I&#39;ve been having trouble articulating the differences between [`ILookup&lt;TKey, TVal&gt;`][1] and [`IGrouping&lt;TKey, TVal&gt;`][2], and am curious if I understand it correctly now.  LINQ compounded the issue by producing sequences of `IGrouping` items while also giving me a `ToLookup` extension method. So it felt like they were the same until I looked more closely.

    var q1 = 
        from n in N
        group n by n.MyKey into g
        select g;
    // q1 is IEnumerable&lt;IGrouping&lt;TKey, TVal&gt;&gt;

Which is equivalent to:

    var q2 = N.GroupBy(n =&gt; n.MyKey, n =&gt; n);
    // q2 is IEnumerable&lt;IGrouping&lt;TKey, TVal&gt;&gt;

Which looks a lot like:

    var q3 = N.ToLookup(n =&gt; n.MyKey, n =&gt; n);
    // q3 is ILookup&lt;TKey, TVal&gt;

Am I correct in the following analogies?

1. An `IGrouping&lt;TKey, TVal&gt;` is a single group (i.e. a keyed sequence), analogous to `KeyValuePair&lt;TKey, TVal&gt;` where the value is actually a sequence of elements (rather than a single element)
1. An `IEnumerable&lt;IGrouping&lt;TKey, TVal&gt;&gt;` is a sequence of those (similar to what you get when iterating over an `IDictionary&lt;TKey, TVal&gt;`
1. An `ILookup&lt;TKey, TVal&gt;` is more like a `IDictionary&lt;TKey, TVal&gt;` where the value is actually a sequence of elements

  [1]: http://msdn.microsoft.com/en-us/library/bb534291.aspx
  [2]: http://msdn.microsoft.com/en-us/library/bb344977.aspx

ILookup&lt;TKey, TVal&gt; vs. IGrouping&lt;TKey, TVal&gt;

I have a command (cmd1) that greps through a log file to filter out a set of numbers. The numbers are 
in random order, so I use sort -gr to get a reverse sorted list of numbers. There may be duplicates within 
this sorted list. I need to find the count for each unique number in that list. 

For e.g. if the output of cmd1 is:

    100 
    100 
    100 
    99 
    99 
    26 
    25 
    24 
    24

I need another command that I can pipe the above output to, so that, I get:


    100     3
    99      2
    26      1
    25      1
    24      2



counting duplicates in a sorted sequence using command line tools

Is there a simple way to count the number of occurrences of all elements of a list into that same list in C#? 

Something like this:
 

    using System;
    using System.IO;
    using System.Text.RegularExpressions;
    using System.Collections.Generic;
    using System.Linq;
        
    string Occur;
    List&lt;string&gt; Words = new List&lt;string&gt;();
    List&lt;string&gt; Occurrences = new List&lt;string&gt;();
    
    // ~170 elements added. . . 
    
    for (int i = 0;i&lt;Words.Count;i++){
        Words = Words.Distinct().ToList();
        for (int ii = 0;ii&lt;Words.Count;ii++){Occur = new Regex(Words[ii]).Matches(Words[]).Count;}
             Occurrences.Add (Occur);
             Console.Write(&quot;{0} ({1}), &quot;, Words[i], Occurrences[i]);
        }
    }

A method to count occurrences in a list

How do I count the number of tr elements within a table using jQuery?

I know there is a [similar question](https://stackoverflow.com/questions/613024/count-number-of-table-rows-between-two-specific-rows-with-jquery), but I just want the total rows.


jQuery: count number of rows in a table

How do I count the number of occurrences of a character in a string?

e.g. `&#39;a&#39;` appears in `&#39;Mary had a little lamb&#39;` 4 times.

Count the number of occurrences of a character in a string

If I had a table like this:

    jobId, jobName, Priority

Whereby Priority can be an integer between 1 to 5.

Since I would need this query for generating a chart on report, I would need to display the jobid, jobname and 5 fields called Priority1, Priority2, Priority3, Priority4. Priority5.  

Priority1 should count the amount of rows where priority field has the value of 1.

Priority2 should count the amount of rows where priority field has the value of 2.

Priority3 should count the amount of rows where priority field has the value of 3.

etc

How would I do that in a quick and performant manner?

Conditional Count on a field

I would like to ask whats the difference between

    for row in session.Query(Model1):
        pass

and


    for row in session.Query(Model1).all():
        pass


is the first somehow an iterator bombarding your DB with single queries and the latter &quot;eager&quot; queries the whole thing as a list (like range(x) vs xrange(x)) ?

Sqlalchemy - Difference between query and query.all in for loops

Does anyone have experience profiling a Python/SQLAlchemy app? And what are the best way to find bottlenecks and design flaws?

We have a Python application where the database layer is handled by SQLAlchemy. The application uses a batch design, so a lot of database requests is done sequentially and in a limited timespan. It currently takes a bit too long to run, so some optimization is needed. We don&#39;t use the ORM functionality, and the database is PostgreSQL.



How can I profile a SQLAlchemy powered application?

I want to do something like this:

    f = Foo(bar=&#39;x&#39;)
    session.add(f)
    session.flush()

    # do additional queries using f.id before commit()
    print f.id # should be not None

    session.commit()

But `f.id` is `None` when I try it. How can I get this to work?



sqlalchemy flush() and get inserted id?

I&#39;m a bit confused about inheritance under sqlalchemy, to the point where I&#39;m not even sure what type of inheritance (single table, joined table, concrete) I should be using here. I&#39;ve got a base class with some information that&#39;s shared amongst the subclasses, and some data that are completely separate. Sometimes, I&#39;ll want data from all the classes, and sometimes only from the subclasses. Here&#39;s an example:

    class Building:
        def __init__(self, x, y):
            self.x = x
            self.y = y

    class Commercial(Building):
        def __init__(self, x, y, business):
            Building.__init__(self, x, y)
            self.business = business

    class Residential(Building):
        def __init__(self, x, y, numResidents):
            Building.__init__(self, x, y, layer)
            self.numResidents = numResidents

How would I convert this to SQLAlchemy using declarative? How, then, would I query which buildings are within `x&gt;5` and `y&gt;3`? Or which Residential buildings have only 1 resident?

SQLAlchemy Inheritance

I&#39;m using SQLalchemy for a Python project, and I want to have a tidy connection string to access my database. So for example:

    engine = create_engine(&#39;postgresql://user:pass@host/database&#39;)

The problem is my password contains a sequence of special characters that get interpreted as delimiters when I try to connect. 

I realize that I could just use `engine.URL.create()` and then pass my credentials like this:

```python
import sqlalchemy as sa

connection_url = sa.engine.URL.create(
    drivername=&quot;postgresql&quot;,
    username=&quot;user&quot;,
    password=&quot;p@ss&quot;,
    host=&quot;host&quot;,
    database=&quot;database&quot;,
)
print(connection_url)
# postgresql://user:p%40ss@host/database
```

But I&#39;d much rather use a connection string if this is possible. 

So to be clear, is it possible to encode my connection string, or the password part of the connection string - so that it can be properly parsed?

Content Type	Original Author	Original Content on Stackoverflow
Question	Nazmul Hasan	View Question on Stackoverflow
Solution 1 - Python	miniwark	View Answer on Stackoverflow
Solution 2 - Python	Jakub Kukul	View Answer on Stackoverflow
Solution 3 - Python	fccoelho	View Answer on Stackoverflow

Group by & count function in sqlalchemy

Python Problem Overview

Python Solutions

Solution 1 - Python

Solution 2 - Python

Solution 3 - Python

How to insert values into C# Dictionary on instantiation?

Are nested span tags OK in XHTML?

Attributions