pythonsqlpandassqlite

Dropping Index in Pandas SQLite Query

I'm using pd.read_sql() to write my queries as I work with an SQLite database with several tables. I'm trying to create a dictionary that includes the table name as the key and the number of rows of the table as each keys' associated value, but the keys keep including the index of the resulting query along with the number of rows. How can I get the values in my dictionary to only include the number of rows without the index?

This is what I have so far:

table_names = list(db_tables['name'])

tables = {key: None for key in table_names}

for table_name in tables.keys():
    sqlite_table = f"SELECT COUNT(*) AS num_rows FROM {table_name}"
    tables[table_name] = pd.read_sql(sqlite_table, conn)
    
tables

And its corresponding output:

{'movie_basics':    num_rows
 0    146144,
 'directors':    num_rows
 0    291174,
 'known_for':    num_rows
 0   1638260,
 'movie_akas':    num_rows
 0    331703,
 'movie_ratings':    num_rows
 0     73856,
 'persons':    num_rows
 0    606648,
 'principals':    num_rows
 0   1028186,
 'writers':    num_rows
 0    255873}

Everything looks fine, except I do not want the zeros included with the values.

Solution

  • If you just want the integer value, you don't need to use pandas for this. Just execute the query directly on the conn:

    tables[table_name] = conn.execute(sqlite_table).fetchone()[0]