Need guidance with python

legendarylegendary
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

  • LoganoLogano

    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
  • legendarylegendary

    @Logano said: I wish I could help

    Thank you for good intention :) it is appreciated.

    Thanked by (1)Logano
  • legendarylegendary

    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
  • axzxc1236axzxc1236
    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.
  • spiritlhlspiritlhl

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

Sign In or Register to comment.