Python SQlite How to check count of imported files

Question

I have imported data into a SQLite3-database with Python. I have 15 tables.

I would like to check the result in each table with a count for each file that was loaded.

Desired Result:

+---------------+------------+-------------------------------------------+
|  Tablename    |  #Records  |              FilenameImported             |
+---------------+------------+-------------------------------------------+
| Tbl_RawData1  |     23680  |  442953-63fe-45fd-8378-c5f5fe5cb755.csv   |
| Tbl_RawData2  |     13358  |  bd3e609f-999f-4d29-845d-107bd5e205d6.csv |
| Tbl_RawData3  |     25000  |  Import.csv                               |
| Tbl_RawData4  |     15000  |  Importbis.csv                            |
+---------------+------------+-------------------------------------------+
...

Below the queries I used in dbeaver.

select count(FileBase), FileBase from Tbl_RawData1 group by Filebase
select count(FileBase), FileBase from Tbl_RawData2 group by Filebase
select count(FileBase), FileBase from Tbl_RawData3 group by Filebase
select count(FileBase), FileBase from Tbl_RawData4 group by Filebase
select count(FileBase), FileBase from Tbl_RawData5 group by Filebase
...

What is the best way of doing this in Python?

Please [edit] to convert your images of text into actual text. [See here](https://meta.stackoverflow.com/a/285557/11107541) for why. See also: ["how to format a table in a post"](https://meta.stackoverflow.com/q/277716/11107541). — starball, Jan 03 '23 at 10:45
You can query the tables inside the SQLite file as [explained here](https://www.sqlitetutorial.net/sqlite-show-tables/). Loop over the resulting table and use each to get count for that table. You can of course filter by table name. — MyICQ, Jan 03 '23 at 10:58
@MyICQ Thanks for your reaction. I want to show the result to the user so I prefer the language of the importprogram namely Python. — Tralala, Jan 03 '23 at 11:33
@Tralala I did link to a page explaining the query. Of course you would write the program in Python to *use* that Query. I did not mean for your result to be executed manually in SQLite. — MyICQ, Jan 03 '23 at 12:00
@MyICQ: your answer in combination with this post (https://stackoverflow.com/questions/305378/list-of-tables-db-schema-dump-etc-using-the-python-sqlite3-api) helped me to find the solution. Thanks! — Tralala, Jan 03 '23 at 12:13

score 0 · Accepted Answer · answered Jan 03 '23 at 12:29

Credits to the answer in this post: List of tables, db schema, dump etc using the Python sqlite3 API

def overview_tables(path, database):
    try:
        dbname = os.path.join(path, database)
        print("INITILIZATION COUNT IMPORT FILES FOR EACH TABLE...")
        con = sqlite3.connect(dbname)
        cursor = con.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        for tbl in tables:
            print("\n########  " + tbl[0] + "  ########")
            # cursor.execute("SELECT * FROM "+tbl[0]+";") #print all columns
            cursor.execute("select count(FileBase), FileBase from " + tbl[0] + " group by FileBase") #print count per geimporteerde file
            rows = cursor.fetchall()
            for row in rows:
                print(row)
        print(cursor.fetchall())
    except KeyboardInterrupt:
        print("\nClean Exit By user")
    finally:
        print("\nFinally")

You can also use f-strings: `cursor.execute(f"select count(FileBase), FileBase from {tbl[0]} group by FileBase")` etc — MyICQ, Jan 03 '23 at 12:33

Python SQlite How to check count of imported files

1 Answers1