automated data import to mysql server with file versioning python script

1files = set()
2for table in owner_tables:
3    if table not in exclude_tables:
4        print('Exporting table: %s' % table)
5        query1 = 'SELECT ' + table + '.* INTO OUTFILE "' + path + 'tables/' + table + '.sql" FROM ' + table + ' JOIN ' \
6                + table + '_owner USING(' + table + '_id) WHERE ' + table + '_owner.scroll_version_id < 1058'
7        cursor.execute(query1)
8 
9    print('Exporting table: %s_owner' % table)
10    query2 = 'SELECT * INTO OUTFILE "' + path + 'tables/' + table + '_owner.sql" FROM ' + table + '_owner WHERE ' \
11             + table + '_owner.scroll_version_id < 1058'
12    cursor.execute(query2)
13    files.add(path + table + '.sql')
14    files.add(path + table + '_owner.sql')
15for table in non_owner_tables:
16    if table not in exclude_tables:
17        query3 = 'SELECT ' + table + '.* INTO OUTFILE "' + path + 'tables/' + table + '.sql" FROM ' + table
18        cursor.execute(query3)
19        files.add(path + table + '.sql')
20

1for file in tables/*.sql; do
2    table=${file%.sql}
3    printf "\rLoading table: ${table##*/}\n"
4    mysql --host=${host} --user=${user} --password=${password} --local-infile ${database} -e "SET FOREIGN_KEY_CHECKS=0;
5    LOAD DATA LOCAL INFILE '$cwd/$file' INTO TABLE ${table##*/};
6    SET FOREIGN_KEY_CHECKS=1;" &
7 
8    pid=$! # Process Id of the previous running command
9 
10    while kill -0 $pid 2>/dev/null
11    do
12      i=$(( (i+1) %4 ))
13      printf "\r${spin:$i:1}"
14      sleep .1
15    done
16done
17

1import mysql.connector
2from mysql.connector.pooling import MySQLConnectionPool
3 
4dbconfig = {'host': "localhost",
5            'user': "username",
6            'password': "password",
7            'database': "database_name"
8            }
9 
10cnxpool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool",
11                                                      pool_size = 30,
12                                                      **dbconfig)
13 
14db = cnxpool.get_connection()
15cursor = db.cursor()
16sql = 'SHOW TABLES'
17cursor.execute(sql)
18result_set = cursor.fetchall()
19path = '/Users/bronson/sqe-mysql-backup/'
20owner_tables = set()
21non_owner_tables = set()
22exclude_tables = {'user', 'user_sessions', 'sqe_session', 'artefact', 'scroll_version',
23                  'external_font_glyph', 'image_to_image_map', 'single_action', 'main_action'}
24for result in result_set:
25    if 'owner' in result[0]:
26        owner_tables.add(result[0].replace("_owner", ""))
27    else:
28        non_owner_tables.add(result[0])
29non_owner_tables = non_owner_tables - owner_tables
30