Python Sql Function, Query To Group Dates By Minutes

I have a SQL database with rows containing prices of EUR/USD for a given datetime: i want to split these data into groups of 1 minute and get some information, I've written a pyth

Solution 1:

Try this:

sql = '''
    SELECT DISTINCT
        f.dm,
        f1.price AS first,
        f.maxp AS max,
        f.minp AS min, 
        f2.price AS last
    FROM (
        SELECT datetime, strftime("%Y-%m-%d %H:%M",datetime) dm,
               MIN(datetime) mindate, MAX(datetime) maxdate,
               MIN(price) minp, MAX(price) maxp
        FROM {c} 
        WHERE datetime >= ? AND datetime < ?
        GROUP BY dm
        ) f
    INNER JOIN {c} f1
    ON f1.datetime = f.mindate
    INNER JOIN {c} f2
    ON f2.datetime = f.maxdate
    '''.format(c = currency)

• dm stands for "date-minute". We group by date-minutes.
• There are some rows with the same datetime. SELECT DISTINCT eliminates duplicate rows.
• The first and last price from each group appear on different rows. To join them on the same row requires a JOIN. This is the purpose of the two INNER JOINS above.

Solution 2:

I recommend that you use MySQLdb Then use fetchmany(8) to get first 8 columns

Here is user guide for MySQLdb: http://mysql-python.sourceforge.net/MySQLdb.html

And here's the download link : http://sourceforge.net/projects/mysql-python

That's what i've understood from you sir

Thank you..

Solution 3:

If you want SQL to do this for you, you'll want to do a GROUP BY minute; then you could use MIN(minp) and MAX(minp) to get the range of the minimum price for each minute. However, it looks like the entire date is one SQL column, so it's not that easy.

SELECT datetime_without_seconds, MIN(minp), MAX(minp) FROM ... 
    GROUPBY datetime_without_seconds

If you can't revise your SQL table schema (or don't want to), it's probably easiest to write a python loop that processes the info you get from your current query.