Home

Peewee Examples Using AstroSQL

astroSQL can be used as a Python module/tool to read, write, and a create MySQL database tables.

Make sure your MySQL is installed and a database has been created.

Table Creation

from astrosql import AstroSQL
from peewee import *

# Fill in your credentials here or use ~/.astrosql/config.yml for better security.
DATABASE = ...
USER = ...
PWD = ...

# Connect to Database
# If config.yml is used, `user` and `password` keyword arguments are not needed.
db = AstroSQL(database=DATABASE, user=USER, password=PWD)

# Building the Schema
class Image(BaseModel):
    filename = CharField(primary_key=True, null=False)
    supernova = CharField(null=True, column_name='supernova') # column_name is optional, by default variable name is used
    ra = DecimalField(max_digits=12, decimal_places=6)
    dec = DecimalField(max_digits=12, decimal_places=6)

    class Meta:
        table_name='image' # optional, by default table name is the classname lowercased.
        database = db.database

Image.create_table()

Write to MySQL Table

# A list of dicts where each dict is a row
DATA1 = [
    { 'image': '2017ein_20172505.fits', 'supernova': '2017ein', 'ra': 641598.75, 'dec': 158846.2},
    { 'image': '2017ein_20172506.fits', 'supernova': '2017ein', 'ra': 641598.75, 'dec': 158846.2},
    { 'image': '2017ein_20172507.fits', 'supernova': '2017ein', 'ra': 641598.75, 'dec': 158846.2},
]

# An array of row arrays
DATA2 = np.array([
    ['2017ein_20172505.fits', 'SN2017ein', 641598.75, 158846.2],
    ['2017ein_20172506.fits', 'SN2017ein', 641598.75, 158846.2],
    ['2017ein_20172507.fits', 'SN2017ein', 641598.75, 158846.2],
])

db = AstroSQL(database=DATABASE, user=USER, password=PWD)
image_table = db.get_table('image')

# Insert one row
db.dict2sql('image', DATA1[0]) # or

query = image_table.insert(DATA1[0])
query.execute()

# Insert many rows
for d in data:
    db.dict2sql('image', d)

# or

query = image_table.insert_many(DATA1)
query.execute()

# Insert many many rows (if above is too slow)
with db.atomic():
    for d in data:
        db.dict2sql('image', d)

# or

with db.atomic():
    image_table.insert_many(DATA1).execute()

# Insert many with array of row arrays
db.array2sql('image',  DATA2)

# or

# Here we must get the field names from somewhere
fields = image_table.database._meta.sorted_fields # or
fields = Image._meta.sorted_fields # or
fields = [Image.filename, Image.supernova, Image.ra, Image.dec]

image_table.insert_many(DATA2, fields=fields).execute()

Another great method is the use of pandas dataframe. You can set up the table as a dataframe and then use df.to_dict('records) to get a list of rows where each row is a dict (just like DATA1)

Reading MySQL Table

# Working with Tables
db.tables.keys() # returns a dict with keys as table names all accessible by db.get_table('table_name')
image_table = db.get_table('image')

# Query table by its column
db.get_by_column(image_table, 'supernova', 'SN2017ein') # args are get_by_column(table, column, query)

# Query table by a square grid designated by RA, DEC, and radius in degrees
db.get_by_radec(image_table, 10, 10, 10)

# Peewee query
image_table.get(image_table.supernova == 'SN2017ein').where(image_table.ra > 10)

# MySQL query
db.