python 3 Store values to a list from separated tables in one sqlite database

Question

I am a beginner in python.

I have several tables (table1 , table2 , table3 ,...) in my database that they have same column TotalPrice.

I want to store TotalPrice of all the tables in one list

TotalPrice is extracted by this code:

for j, v in enumerate(busList):
                mc.execute('SELECT TotalPrice FROM bus'+v+' WHERE Date BETWEEN '+StartDate+' AND '+EndDate+'')
                p = mc.fetchall()
                price = [i[0] for i in p]

but i don't know how to store them just in ONE list!!!!!

score 1 · Accepted Answer · answered Jul 23 '18 at 16:10

Use UNION ALL, it will perform better as it will be a single query sent to the database:

mc.execute(' UNION ALL '.join(
    'SELECT TotalPrice FROM bus{} WHERE Date BETWEEN ? AND ?'.format(v) 
        for v in busList), 
    (StartDate, EndDate) * len(busList),
)

Note that this also use parameterization for the dates instead of string interpolation, thus besides also performing better, frees you from quote hell and prevents all forms of injection.

score 0 · Answer 2 · answered Jul 23 '18 at 16:05

You can use append() method of list (or extend, it depends what do you want - Difference between append vs. extend list methods in Python)

price_list = []
for j, v in enumerate(busList):
    mc.execute('SELECT TotalPrice FROM bus'+v+' WHERE Date BETWEEN '+StartDate+' AND '+EndDate+'')
    p = mc.fetchall()
    price_list.append([i[0] for i in p])

After that, the price_list will contain lists with all prices from the tables.

score 0 · Answer 3 · answered Jul 23 '18 at 16:17

You can directly append to an empty list as suggested by @Andrej or else use this approach

import pandas as pd
import sqlite3

conn = sqlite3.connect("db")
df = pd.read_sql_query("select TotalPrice from table1 Union all
                        select TotalPrice from table2;", conn)
myList = df['TotalPrice'].tolist()

python 3 Store values to a list from separated tables in one sqlite database

3 Answers3