Need guidance with python

legendary · May 2022

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).

Logano · May 2022

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.

legendary · May 2022

@Logano said: I wish I could help

Thank you for good intention it is appreciated.

legendary · May 2022

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()
<...>

axzxc1236 · May 2022

Not related to solving a problem, but:

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.
columns can be a set
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.

spiritlhl · May 2022

@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.

Need guidance with python

Comments