Created
October 25, 2017 20:25
-
-
Save andy-esch/3fb9ddfe8d573841a8ac8ff70e9e29e1 to your computer and use it in GitHub Desktop.
prototype function / class from v0.0001 cartoframes
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| """Class for updating a cartoframe postgres table""" | |
| class SQL(object): | |
| """class for building queries for syncing""" | |
| def __init__(self, cartoframe): | |
| """""" | |
| self.cframe = cartoframe | |
| def add_cols(self): | |
| """prototype methods for add columns to table/dataframe""" | |
| newcols = list(set(self.cframe.columns) - | |
| set(self.cframe.last_state.columns)) | |
| pgtypes = [pg_type(t, c) for t, c in zip(self.cframe[newcols].dtypes, | |
| newcols)] | |
| addcol_temp = 'ADD COLUMN "{colname}" {pgtype}' | |
| addcols = [addcol_temp.format(colname=c, | |
| pgtype=p) for c, p in zip(newcols, | |
| pgtypes)] | |
| query = ''' | |
| ALTER TABLE "{tablename}" | |
| {addcols}; | |
| '''.format(tablename=self.cframe.get_carto_tablename(), | |
| addcols=addcols) | |
| return query | |
| def drop_cols(self): | |
| """Drops columns removed from dataframe""" | |
| oldcols = list(set(self.cframe.last_state.columns) - | |
| set(self.cframe.columns)) | |
| dropcol_temp = 'DROP COLUMN "{colname}"' | |
| dropcols = [dropcol_temp.format(colname=c) for c in oldcols] | |
| query = ''' | |
| ALTER TABLE "{tablename}" | |
| {dropcols}; | |
| '''.format(tablename=self.cframe.get_carto_tablename(), | |
| dropcols=dropcols) | |
| return query | |
| def update_vals(self): | |
| """Update cells which have changed since last sync""" | |
| query = ('UPDATE "{tablename}" AS a ' | |
| 'SET {setlist} ' | |
| 'FROM {temptable} AS t ' | |
| 'WHERE a.cartodb_id = t.id;') | |
| return query | |
| def pg_type(dtype, colname): | |
| """Maps dataframe type to postgres type""" | |
| return dtype + colname | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment