How to get table name of the executed query? (python / sqlite)

Question

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?

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
– wils484, Commented Apr 10, 2014 at 0: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
– DominicM, Commented Apr 10, 2014 at 0:39

Lionel Brooks · Accepted Answer · 2014-04-16 21:15:26Z

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

answered Apr 16, 2014 at 21:15

Lionel Brooks

2682 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DominicM Over a year ago

Was hoping to avoid any extra sql but I guess that might the the only way to do this. Thanks.

Rocketq Over a year ago

@LionelBrooks Can this parser be used without actually connecting database?

Collectives™ on Stack Overflow

How to get table name of the executed query? (python / sqlite)

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related