automated data import to mysql server with file versioning python script

files = set() for table in owner_tables: if table not in exclude_tables: print('Exporting table: %s' % table) query1 = 'SELECT ' + table + '.* INTO OUTFILE "' + path + 'tables/' + table + '.sql" FROM ' + table + ' JOIN ' \ + table + '_owner USING(' + table + '_id) WHERE ' + table + '_owner.scroll_version_id < 1058' cursor.execute(query1) print('Exporting table: %s_owner' % table) query2 = 'SELECT * INTO OUTFILE "' + path + 'tables/' + table + '_owner.sql" FROM ' + table + '_owner WHERE ' \ + table + '_owner.scroll_version_id < 1058' cursor.execute(query2) files.add(path + table + '.sql') files.add(path + table + '_owner.sql') for table in non_owner_tables: if table not in exclude_tables: query3 = 'SELECT ' + table + '.* INTO OUTFILE "' + path + 'tables/' + table + '.sql" FROM ' + table cursor.execute(query3) files.add(path + table + '.sql')

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

import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
 
dbconfig = {'host': "localhost",
            'user': "username",
            'password': "password",
            'database': "database_name"
            }
 
cnxpool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool",
                                                      pool_size = 30,
                                                      **dbconfig)
 
db = cnxpool.get_connection()
cursor = db.cursor()
sql = 'SHOW TABLES'
cursor.execute(sql)
result_set = cursor.fetchall()
path = '/Users/bronson/sqe-mysql-backup/'
owner_tables = set()
non_owner_tables = set()
exclude_tables = {'user', 'user_sessions', 'sqe_session', 'artefact', 'scroll_version',
                  'external_font_glyph', 'image_to_image_map', 'single_action', 'main_action'}
for result in result_set:
    if 'owner' in result[0]:
        owner_tables.add(result[0].replace("_owner", ""))
    else:
        non_owner_tables.add(result[0])
non_owner_tables = non_owner_tables - owner_tables

Posted by: Guest on November-08-2020

Source

Code answers related to "automated data import to mysql server with file versioning python script"

Browse Popular Code Answers by Language

Answers for "automated data import to mysql server with file versioning python script"

Code answers related to "automated data import to mysql server with file versioning python script"

Code answers related to "SQL"

Browse Popular Code Answers by Language

Popular Programming Languages

Advertisements

Company

Compilers

Help

Connect with us