Need guidance with python

edited May 2022 in Help

Now, I have script which imports data into SQLite3 database from .json file. Current code:

# Auto-discover columns
columns = []
column = []

for data in json_data:
    column = list(data.keys())
    for col in column:
        if col not in columns:
            columns.append(col)

# Auto-bind values to discovered columns
value = []
values = []

for data in json_data:
    for i in columns:
        value.append(str(dict(data).get(i)))
    values.append(list(value))
    value.clear()

insert_query = 'insert into logs values (null,:date,:server,:ip,:mta,:country,:env_from,:from,:subject,:spam_type,:mqid,:sender_domain,:auth_id,:verdict,:class,:score,:raw,:pure_log)'

c = db.cursor()
c.executemany(insert_query, values)
db.commit()

There is 2 data points which need pre-processing before insertion: 'env_from' and 'subject'. How to do that? I guess I need to modify '# Auto-bind values to discovered columns' part of code. But whatever I do - it just rejects with error. Specifically I want to decode strings in utf8 mime (email).

Thanked by (1)Logano

Comments

  • I wish I could help, but I haven't taken or read any of the 50-plus Python courses and books that are in my backlog.

    Thanked by (1)legendary
  • @Logano said: I wish I could help

    Thank you for good intention :) it is appreciated.

    Thanked by (1)Logano
  • Ha, found a solution:

    <...>
    # Auto-bind values to discovered columns
    value = []
    values = []
    
    for data in json_data:
        for i in columns:
            value.append(str(make_header(decode_header(str(dict(data).get(i))))))
        values.append(list(value))
        value.clear()
    <...>
    
    Thanked by (2)Ympker Logano
  • edited May 2022

    Not related to solving a problem, but:

    1. Give an example data and example wanted result would be much helpful, people can't extract much information from just "need pre-processing", still don't understand where "make_header()" and "decode_header()" in your solution come from.
    2. columns can be a set
    3. Instead of hard-coding SQL commands in your script, I would recommend learning a python library with ORM (Object Relational Mapping) functionality in the future, Django for example.
  • @axzxc1236 said:
    3. Instead of hard-coding SQL commands in your script, I would recommend learning a python library with ORM (Object Relational Mapping) functionality in the future, Django for example.

    I would recommend using FastAPI with sqlalchemy to achieve a similar effect, which I find a bit more lightweight.

    My virtualized amusement park: https://github.com/oneclickvirt

Sign In or Register to comment.