Growing stuff and doing things

dstroy0

dstroy0

Zeroes and Ones
1583779833865.png

got this script working to insert my jsons into an sql database, grows with unknown columns

I take a deeply nested json doc and flatten it, then generate the insert query after scraping the key / value pairs, during the query generation I pull a list of the existing columns from the database and if a column that was received isn't in the working table it gets added
 
spyralout

spyralout

🌱🌿🌲🔥💨
Staff member
Administrator
Moderator
dstroy0 said:
View attachment 3479

got this script working to insert my jsons into an sql database, grows with unknown columns

I take a deeply nested json doc and flatten it, then generate the insert query after scraping the key / value pairs, during the query generation I pull a list of the existing columns from the database and if a column that was received isn't in the working table it gets added
Click to expand...
blink.gif
 
spyralout

spyralout

🌱🌿🌲🔥💨
Staff member
Administrator
Moderator
spyralout said:
View attachment 3480
Click to expand...
OK, screw it, I'm gonna take a stab at interpreting this. I have no SQL database experience, only some simple MS Access queries.

@dstroy0, if I understand correctly, you took a logical table file from the database, and made it a flat file (all data, no keys). From there your script looked for key values to link one attribute to another to get the tables to "talk to each other" correctly. If the query finds a key value that didn't already have a column in the newly generated table in your SQL database, it adds that column to the table accordingly.
 
curious2garden

curious2garden

In Bloom
dstroy0 said:
View attachment 3479

got this script working to insert my jsons into an sql database, grows with unknown columns

I take a deeply nested json doc and flatten it, then generate the insert query after scraping the key / value pairs, during the query generation I pull a list of the existing columns from the database and if a column that was received isn't in the working table it gets added
Click to expand...
Python looks a lot like C. I keep thinking in my spare time I'll take a look :LOL: I've enjoyed watching your progress.
 
dstroy0

dstroy0

Zeroes and Ones
spyralout said:
OK, screw it, I'm gonna take a stab at interpreting this. I have no SQL database experience, only some simple MS Access queries.

@dstroy0, if I understand correctly, you took a logical table file from the database, and made it a flat file (all data, no keys). From there your script looked for key values to link one one attribute to another to get the tables to "talk to each other" correctly. If the query finds a key value that didn't already have a column in the newly generated table in your SQL database, it adds that column to the table accordingly.
Click to expand...

It could be described as a json deserializer I think? I'm no expert.

in a valid json you can make a key reference the python equivalent of a dict so it will prepend that key to the child keys inside like this

ENV:{A1:{CO2:0}} to ->ENV_A1_CO2<- legal column name and bind that to its value "0"

then, say you get a different message it will add that column no problem

but the depth of the json doesn't matter, each level into the json is an underscore in the column name, so it is scalable up to the allowed column char limit and I don't know shit about SQL either so I'm surprised this works
 
dstroy0

dstroy0

Zeroes and Ones
Here's that python script I'm working on

Code: 
#! /usr/bin/python3

import serial
import json
import sqlite3
from datetime import datetime

#specify database
db_conn = sqlite3.connect('/home/pi/Desktop/python_scripts/sensor.db')

#constants
BAUD_RATE = 500000
RAW_INPUT_STRING_SLICE_START = 12

#figure out if the object is a valid json
def is_json(myjson):
    try:
        #leverage json.loads to tell us what's wrong with the json, if anything
        json_object = json.loads(myjson)
    except ValueError as e:     
        #useful error report
        print('ERROR is_json() :',e,"for object :")
        print(myjson,"\n")
        return False
    return True

#only pass valid json to this func
def flatten_json(nested_json):
    """
        Flatten json object with nested keys into a single level.
        Args:
            nested_json: A nested json object.
        Returns:
            The flattened json object if successful, None otherwise.
    """
    out = {}

    def flatten(x, name=''):
        if type(x) is dict:
            for a in x:
                flatten(x[a], name + a + '_')
        elif type(x) is list:
            i = 0
            for a in x:
                flatten(a, name + str(i) + '_')
                i += 1
        else:
            out[name[:-1]] = x

    flatten(nested_json)
    return out

#pass valid json to this func
def commit_json(json_data):
    #normalize and flatten
    json_data = json.loads(json_data)
    json_data = flatten_json(json_data)
    
    #var
    columns = []
    values = []
    c = db_conn.cursor()
    #pull existing columns from db
    db_columns = [i[1] for i in c.execute('PRAGMA table_info(myTable)')]
    
    #get key:value pairs
    for column, value in json_data.items():        
        #slice the first 12 char off each column name, which is the controller version in my schema
        if str(column[RAW_INPUT_STRING_SLICE_START:]) not in db_columns:
            try:
                add_column = "ALTER TABLE myTable ADD COLUMN {0}".format(" TEXT".join((str(column[RAW_INPUT_STRING_SLICE_START:]),)))
                #print db query for verif
                print(add_column)
                #add unknown column
                c.execute(add_column)
            except ValueError as e:
                print("ERROR commit_json()->add_column : ", e)
        columns.append(str(column[RAW_INPUT_STRING_SLICE_START:]))
        values.append(str(value)) 
    
    #uncomment for num bindings
    #print(len(columns)-1)
    
    #uncomment for flat key:value pairs    
    #print(columns, values)
    
    create_query = "create table if not exists myTable({0})".format(" text, ".join(columns))
    insert_query = "INSERT INTO myTable({0}) VALUES(?{1})".format(", ".join(columns), ",?" * (len(columns)-1))
    
    #uncomment for create_query format verif
    #print(create_query)
    #uncomment for insert_query format verif
    print(insert_query)
    
    #print insert start time
    print("insert has started at " + str(datetime.now()))
    
    #make the table if it doesnt exist
    c.execute(create_query)
    #insert rows into database
    c.executemany(insert_query, (values,))
    #empty out values
    values.clear()
    #commit changes to db
    db_conn.commit()
    #close db cursor
    c.close()
    
    #print insert finish time
    print("insert has completed at " + str(datetime.now()) + "\n")
        
        
def main():
    #UART port
    ser = serial.Serial('/dev/ttyS0', BAUD_RATE, timeout=5) #open the serial port        
    
    #sentinel
    var = 1
    #main loop
    while var  == 1:
        #read from stream into line
        line = ser.readline()
        #remove byte type and carriage return
        line = line.decode('ascii')
        line = line.rstrip()
        #wrap object
        line = '[' + line + ']'
        
        #uncomment to view raw object
        #print(line) #print 'line'
                
        #test if json in 'line' is valid json        
        if is_json(line) == True:
            try:
                commit_json(line)
            except ValueError as e:
                print('ERROR main() :',e)
#run
main()
 
spyralout

spyralout

🌱🌿🌲🔥💨
Staff member
Administrator
Moderator
You must log in or register to reply here.

Similar threads

G
Blue Star OG pheno hunt, PPK style
2
Replies
30
Views
2K
doober
doober
greenmachine29
Lemons and limes. Sprite chucking project.
Replies
9
Views
536
JL2G
JL2G
S
Sleazy' s Struggle Bus
4 5 6
Replies
112
Views
4K
sleazyB
S
Biskit420
Hello from Ohio
2
Replies
23
Views
450
Trial n Error
Trial n Error
District_Gardens_Beans88
My Indoor DMV Garden
2 3
Replies
41
Views
874
HydroRed
HydroRed
Top Bottom