How to restore MySQL database using Python-CodePudding

I have a sql file generated during database backup process and I want to load all database content from that sql file to a different MySQL database (secondary database).

I have created a python function to load the whole database in that sql file but when I execute the function, I get an error

'str' object is not callable

Below is python script

def load_database_dump_to_secondary_mysql(file_path='db_backup_file.sql'):
        query = f'source {file_path}'
        try:
            connection = mysql_hook.get_conn() # connection to secondary db
            cursor = connection.cursor(query)
            print('LOAD TO MYSQL COMPLETE')
        except Exception as xerror:
            print("LOAD ERROR: ", xerror)

NB: mysql_hook is an airflow connector that contains MySQL DB connection info such as Host, user/passwd, Database name. Also, I don't have connection to the primary database, I'm only receiving sql dump file.

What I'm I missing?

CodePudding user response：

source is a client builtin command: https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

It's not an SQL query that MySQL's SQL parser understands.

So you can't execute source using cursor.execute(), because that goes directly to the dynamic SQL interface.

You must run it using the MySQL command-line client as a subprocess:

subprocess.run(['mysql', '-e', f'source {file_path}'])

You might need other options to the mysql client, such as user, password, host, etc.

CodePudding user response：

try this

import mysql.connector as m

# database which you want to backup
db = 'geeksforgeeks'

connection = m.connect(host='localhost', user='root',
                    password='123', database=db)
cursor = connection.cursor()

# Getting all the table names
cursor.execute('SHOW TABLES;')
table_names = []
for record in cursor.fetchall():
    table_names.append(record[0])

backup_dbname = db   '_backup'
try:
    cursor.execute(f'CREATE DATABASE {backup_dbname}')
except:
    pass

cursor.execute(f'USE {backup_dbname}')

for table_name in table_names:
    cursor.execute(
        f'CREATE TABLE {table_name} SELECT * FROM {db}.{table_name}')