How to programmatically convert multiple db files to csv in python or R?

Question

I have thousands of *.db files containing sensor data. All *.db files have the same structure like, ID,MACID,RSSI,ENTRY,EXIT,DEVICEID,UNIQUEID. I have seen a similar question like this one but the solution does not work for me.

What I have tried so far:

1. Initially tried DB Browser for SQLite to individually convert each *.db to .csv format. It's a lot of copy-paste and I hated it!

2. Using online forums, created a makeshift script as follows;

import sqlite3
from sqlite3 import Error

import pandas as pd
dataPath = "path_to_a_single_db_file"
# create database connection
conn = sqlite3.connect(dataPath)
# a custom function to create database connection
# Reference: http://www.sqlitetutorial.net/sqlite-python/sqlite-python-select/
def create_connection(db_file):
              try:
                   conn = sqlite3.connect(db_file)
                    return conn
              except Error as e:
                    print(e)

              return None
# create a function for reading & writing the Wifi table

def read_write_wifi_tbl_to_csv(conn):
            cur = conn.cursor()
            query='select * from WIFI'
            data=pd.read_sql(query,conn)
            data.to_csv('export.csv')

def main():
    # create a database connection
    conn = create_connection(dataPath)
    with conn:
      print("1. Query and writing to Wifi table")
      read_write_wifi_tbl_to_csv(conn)

if __name__ == '__main__':
                main()

The above code snippet works well for a single db file. How to make this work for multiple db files.

Answer 1

If you use python,

Use sqlite and csv packages to convert each sqlite .db file to csv file.

Then use pandas to read all csv files and write to single csv file using pandas.

EDIT: well, pandas is not required you can directly write to single csv file.

EDIT 2: Here is a quick notebook for that: https://github.com/darshanz/CombineMultipleSqliteToCsv