dump mysql table as jsonl file

mysql_json_table_dump.py

#!/usr/bin/env python
import json
import pymysql.cursors

def main():
    # Connect to the database
    connection = pymysql.connect(
        host='localhost',
        user='changeme',
        password='changeme',
        db='changeme',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.SSDictCursor # we use SSDictCursor for streaming rows (saves ram)
    )


    try:
        with open('table.json', 'a+') as f:
            cursor = connection.cursor()
            sql = 'select * from `table`;'
            relnum = cursor.execute(sql)
            result = cursor.fetchone()
            while result is not None:
                db_record = {}
                for k,v in result.items():
                    db_record[k] = v

                # Write to json outfile
                f.write(f'{json.dumps(db_record)}\n')
                result = cursor.fetchone()
    finally:
        cursor.close()
        connection.close()

if __name__ == '__main__':
    main()