1

I am running a simple query and converting results to json. I would like to do this dynamically so I can use one function to handle all queries. 

query = "INSERT INTO Tests (name, start, end) VALUES (?, ?, ?)"
params = (name, start, end)  
conn = sqlite3.connect('settings.db')
cur = conn.cursor()
cur.execute(query, params)
conn.commit()
rows = cur.fetchall()

I am getting column names by using cursor.description but I also need the table names to achieve json structure like below:

{ status: 'success', tests: [ name: 'sample', 'start': '8484', 'end': '9054' ], [ name: 'sample2', 'start': '84842', 'end': '90542' ] }

Is there a reasonable way to achieve this?

DominicM
  • 6,520
  • 13
  • 39
  • 60
  • 1
    Why not put the table name into a variable and add it to your results set? You need to know the name of the table ahead of time to query it, so why do you need to get it through a results set? – wils484 Apr 10 '14 at 00:27
  • That's one solution, but it's not ideal as that's another variable to create and to pass to the function and you can't use it for parametrisation so it would be only for creation of json. – DominicM Apr 10 '14 at 00:39

1 Answers1

2

Here is a method to get the table names from your database connection. I hope that it is useful for you.

cur.execute('''select * from sqlite_master''')
l = cur.fetchall()
#we will populate a list with the names of your tables
tbl_name_list = []
for sql_type, sql_name, tbl_name, rootpage, sql in l:
    if sql_type == 'table':
        tbl_name_list.append(sql_name)
#your list of table names is constructed
print(tbl_name_list)

for more info: https://sqlite.org/faq.html#q7

Lionel Brooks
  • 258
  • 2
  • 7