CODEWITHSUNDEEP
pythonsqlite
Runner Bean
Runner Bean

Reputation: 5165

sqlite python - reducing repetitive code or handling the relationship on the database side

I have this huge for loop where it seems very repitive, is there a way to reduce this code.

Or, should *handling the relationship on the database side as perhaps this is bad coding practice?

import  sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

c.execute('''CREATE TABLE table_one
                (c_1, c_2,  c_3)''')

t1_items = [    ('a',    'c',     2),
                ('a',    'd',     3),
                ('a',    'e',     4),
                ('b',    'e',     5),]

c.executemany('INSERT INTO table_one VALUES (?,?,?)', t1_items)

c.execute('''CREATE TABLE table_two
                (c_1, c_2,  c_3,  c_4,  c_5,  c_6,  c_7,
                c_8,  c_9,  c_10, c_11, c_12, c_13, c_14,
                c_15, c_16, c_17, c_18, c_19, c_20, c_21,
                c_22, c_23, c_24, c_25, c_26, c_27, c_28,
                c_29, c_30, c_31, c_32, c_33, c_34, c_35,
                c_36, c_37, c_38, c_39, c_40)''')

t2_items = [    ('c', 2, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 7, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 3, 2, 1, 4),
                ('d', 1, 3, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 4, 1, 1, 1, 1, 2, 6, 1, 1, 1, 1, 6, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1),
                ('e', 1, 1, 1, 4, 1, 1, 1, 2, 5, 1, 3, 1, 1, 1, 5, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 5, 1, 1, 1, 1, 1, 1, 1, 1, 7, 1),]

c.executemany('''INSERT INTO table_two VALUES
                (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', t2_items)

c.execute('''CREATE TABLE table_three
                (c_1, c_2,  c_3,
                v_1, v_2,  v_3,  v_4,  v_5,  v_6,  v_7,
                v_8,  v_9,  v_10, v_11, v_12, v_13, v_14,
                v_15, v_16, v_17, v_18, v_19, v_20, v_21,
                v_22, v_23, v_24, v_25, v_26, v_27, v_28,
                v_29, v_30, v_31, v_32, v_33, v_34, v_35,
                v_36, v_37, v_38, v_39, v_40)''')

sql = """SELECT t.c_1, t.c_2,  t.c_3,
                s.c_1,  s.c_2,  s.c_3,  s.c_4,  s.c_5,  s.c_6,  s.c_7,
                s.c_8,  s.c_9,  s.c_10, s.c_11, s.c_12, s.c_13, s.c_14,
                s.c_15, s.c_16, s.c_17, s.c_18, s.c_19, s.c_20, s.c_21,
                s.c_22, s.c_23, s.c_24, s.c_25, s.c_26, s.c_27, s.c_28,
                s.c_29, s.c_30, s.c_31, s.c_32, s.c_33, s.c_34, s.c_35,
                s.c_36, s.c_37, s.c_38, s.c_39, s.c_40
         FROM table_one t
         INNER JOIN table_two s
             ON t.c_2 = s.c_1"""

result = c.execute(sql).fetchall()

for row in result:
    v_1 = row[3]*row[2]
    v_2 = row[4]*row[2]
    v_3 = row[5]*row[2]
    v_4 = row[6]*row[2]
    v_5 = row[7]*row[2]
    v_6 = row[8]*row[2]
    v_7 = row[9]*row[2]
    v_8 = row[10]*row[2]
    v_9 = row[11]*row[2]
    v_10 = row[12]*row[2]
    v_11 = row[13]*row[2]
    v_12 = row[14]*row[2]
    v_13 = row[15]*row[2]
    v_14 = row[16]*row[2]
    v_15 = row[17]*row[2]
    v_16 = row[18]*row[2]
    v_17 = row[19]*row[2]
    v_18 = row[20]*row[2]
    v_19 = row[21]*row[2]
    v_20 = row[22]*row[2]
    v_21 = row[23]*row[2]
    v_22 = row[24]*row[2]
    v_23 = row[25]*row[2]
    v_24 = row[26]*row[2]
    v_25 = row[27]*row[2]
    v_26 = row[28]*row[2]
    v_27 = row[29]*row[2]
    v_28 = row[30]*row[2]
    v_29 = row[31]*row[2]
    v_30 = row[32]*row[2]
    v_31 = row[33]*row[2]
    v_32 = row[34]*row[2]
    v_33 = row[35]*row[2]
    v_34 = row[36]*row[2]
    v_35 = row[37]*row[2]
    v_36 = row[38]*row[2]
    v_37 = row[39]*row[2]
    v_38 = row[40]*row[2]
    v_39 = row[41]*row[2]
    v_40 = row[42]*row[2]
    item = [(row[0], row[1], row[2],
             v_1, v_2,  v_3,  v_4,  v_5,  v_6,  v_7,
             v_8,  v_9,  v_10, v_11, v_12, v_13, v_14,
             v_15, v_16, v_17, v_18, v_19, v_20, v_21,
             v_22, v_23, v_24, v_25, v_26, v_27, v_28,
             v_29, v_30, v_31, v_32, v_33, v_34, v_35,
             v_36, v_37, v_38, v_39, v_40),]
    c.executemany('''INSERT INTO table_three VALUES
                    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', item)

conn.commit()

for row in c.execute('SELECT * FROM table_three'):
    print(row)

conn.close()

The code runs but I think maybe the main operation in the for loop should be replaced somehow to handle the relationship on the database side? Or just cut down the for loop with some function? Im stuck here!

EDIT: In the real code all the v_1, v_2, ... have very different unique names

EDIT 2: So i changed the code as per the answer below to

sql = """INSERT INTO table_three(c_1, c_2, c_3, v_1, v_2,  v_3,  v_4,  v_5,  v_6,  v_7, v_8,  v_9,  v_10, v_11, v_12, v_13, v_14, v_15, v_16, v_17, v_18, v_19, v_20, v_21, v_22, v_23, v_24, v_25, v_26, v_27, v_28, v_29, v_30, v_31, v_32, v_33, v_34, v_35, v_36, v_37, v_38, v_39, v_40)
SELECT
    t.c_1,
    t.c_2,
    t.c_3,
    s.c_1 * t.c_3,
    s.c_2 * t.c_3,
    s.c_3 * t.c_3,
    s.c_4 * t.c_3,
    s.c_5 * t.c_3,
    s.c_6 * t.c_3,
    s.c_7 * t.c_3,
    s.c_8 * t.c_3,
    s.c_9 * t.c_3,
    s.c_10 * t.c_3,
    s.c_11 * t.c_3,
    s.c_12 * t.c_3,
    s.c_13 * t.c_3,
    s.c_14 * t.c_3,
    s.c_15 * t.c_3,
    s.c_16 * t.c_3,
    s.c_17 * t.c_3,
    s.c_18 * t.c_3,
    s.c_19 * t.c_3,
    t.c_30 * t.c_3,
    t.c_31 * t.c_3,
    t.c_32 * t.c_3,
    t.c_33 * t.c_3,
    t.c_34 * t.c_3,
    t.c_35 * t.c_3,
    t.c_36 * t.c_3,
    t.c_37 * t.c_3,
    t.c_38 * t.c_3,
    t.c_39 * t.c_3,
    s.c_30 * t.c_3,
    s.c_31 * t.c_3,
    s.c_32 * t.c_3,
    s.c_33 * t.c_3,
    s.c_34 * t.c_3,
    s.c_35 * t.c_3,
    s.c_36 * t.c_3,
    s.c_37 * t.c_3,
    s.c_38 * t.c_3,
    s.c_39 * t.c_3,
    s.c_40 * t.c_3,
FROM table_one t
INNER JOIN table_two s
    ON t.c_2 = s.c_1;"""

c.execute(sql)

but now i get the error

c.execute(sql)
sqlite3.OperationalError: near "FROM": syntax error

Upvotes: 0

Views: 48

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521209

This is massively inefficient, because it is forcing you to materialize two entire result sets into Python's memory, only to write that same modified information out to your SQL database again. Try using an INSERT INTO ... SELECT here:

INSERT INTO table_three (c_1, c_2, c_3, v_1, v_2, ..., v_40)
SELECT
    t.c_1,
    t.c_2,
    t.c_3,
    t.c_3 * t.c_2,
    t.c_4 * t.c_2,
    ...
    t.c_42 * t.c_2
FROM table_one t
INNER JOIN table_two s
    ON t.c_2 = s.c_1;

I did not give Python code, and my query might be slightly off (I see a column count discrepancy in the code you posted). The basic idea here is to avoid the result set from the join select between the first two tables from ever having to leave the database, and make a round trip to and from your Python application.

Upvotes: 1

Related Questions