[AstroPy] Storing results into Tables for further SQL queries.

[Covino, Stefano]

Probably something like this could work:è

import mysql.connector as pm
from astropy.table import Table


def readMySQLTable (ihost, idbase, iuser, ipwd, icmd):
    db = pm.connect(user=iuser, password=ipwd, host=ihost, database=idbase,
connect_timeout=60)
    cursor = db.cursor()
    cursor.execute(icmd)
    cols = cursor.column_names
    dt = [a for a in cursor]
    t = Table(rows=dt,names=cols)
    return t

Where “icmd” is the sql command.

   Stefano



Il giorno lunedì 9 settembre 2019, keith <keith at sloan-home.co.uk> ha
scritto:

> Below is the sort of thing I would like to do from python for both GAMA
> and SDSS
>
> It is an example of my use of the SDSS CasJob facility I would like to do
> the same from python and similar accessing GAMA. The way the CasJob seems
> to work is to store to an external database as the query specifies into mydb.E_Galaxies
> where mydb is particular to my login and the other tables are available
> i.e. DR15.Galaxy
>
> Having created mydb.E_Galaxies I would then use the table in future joins
> etc without having to repeat the execution for a large number of future
> queries.
>
> SELECT TOP 5000 ObjID,r,g,i,u,u-r as redness into mydb.E_Galaxies from
> DR15.Galaxy as G
> WHERE
> G.lnlDev_r > G.lnlExp_r + 0.1
> and G.lnlExp_r > -999
>
> and (G.flags & (dbo.fPhotoFlags('BINNED1') + dbo.fPhotoFlags('BINNED2') +
> dbo.fPhotoFlags('BINNED4'))) > 0
>
> and (G.flags & ( dbo.fPhotoFlags('BLENDED') + dbo.fPhotoFlags('NODEBLEND')
> + dbo.fPhotoFlags('CHILD'))) != dbo.fPhotoFlags('BLENDED')
>
> and (G.flags & (dbo.fPhotoFlags('EDGE') + dbo.fPhotoFlags('SATURATED'))) =
> 0
>
> and G.petroMag_i > 17.5
>
> and (G.petroMag_r > 15.5 or G.petror50_r > 2)
> and (G.petroMag_r > 0 and G.g > 0 and G.r > 0 and G.i > 0)
> and ( (G.petroMag_r - G.extinction_r) < 19.2
> and (G.petroMag_r - G.extinction_r <
> (13.1 + (7/3)*(G.g - G.r) + 4 *(G.r - G.i) - 4 * 0.18 ) )
> and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) < 0.2 )
> and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) > -0.2 ) )
> or ( (G.petroMag_r - G.extinction_r < 19.5)
> and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) >
> (0.45 - 4*(G.g - G.r) ) )
> and ( (G.g - G.r) > (1.35 + 0.25 *(G.r - G.i) ) ) )
> On 09/09/2019 18:33, Aldcroft, Thomas wrote:
>
> Hi Keith,
>
> With the astropy Table object you can do table operations like join and
> merge:
>
> https://docs.astropy.org/en/stable/table/operations.html#table-operations
>
> I'm not entirely clear if your question is about how to get your SQL query
> results into a Table, or how to do joins with a Table.  Can you provide a
> bit more detail?
>
> - Tom
>
> On Sun, Sep 8, 2019 at 10:44 AM keith <keith at sloan-home.co.uk> wrote:
>
>> Apologies for a newby question.
>>
>> For my MSc project I need  to run SQL queries against GAMA and SDSS.
>>
>> I am using Jupyter on Ubuntu 18.04.
>>
>> I can successfully retrieve data by using the astropy python modules.
>>
>> I would like to store the results from the more complicated SQL queries
>> into a Table,
>> so that I can perform further joins on both GAMA and SDSS  tables.
>>
>> How do I go about this?
>>
>> Do I have to setup a local database?
>>
>> Thanks
>>
>> --
>> ========== Art & Ceramics ===========
>> https://www.instagram.com/ksloan1952/
>>
>> _______________________________________________
>> AstroPy mailing list
>> AstroPy at python.org
>> https://mail.python.org/mailman/listinfo/astropy
>>
>
> _______________________________________________
> AstroPy mailing listAstroPy at python.orghttps://mail.python.org/mailman/listinfo/astropy
>
> --
> ========== Art & Ceramics ===========https://www.instagram.com/ksloan1952/
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/astropy/attachments/20190909/84369b4e/attachment-0001.html>