How to batch modify database to execute Sql file with Python

preface

Due to batch modified the document in the last article, there are times when a database also needs to be revised 1, volume is derived using the pagoda phpMyAdmin before 1 has been modified sql file, and then in turn to other database import, efficiency is not very low, is low, and is all some repetitive labor, so plan to use sql Python to batch execution

The environment

Version: Python3. 6 System: MacOS IDE:PyCharm Third square library :pymysql

Show Code

import pymysql

host = 'xxx.65.9.191'
username = 'root'
password = 'root'

def connectMySQL():
 print(' Start connecting to the database ')
 #  Open database connection 
 db = pymysql.connect(host,username,password,charset='utf8')

 #  use  cursor()  Method to create 1 A cursor object  cursor
 cursor = db.cursor()

 #  use  execute()  Display all databases 
 cursor.execute("SHOW DATABASES")
 print(' Start querying all databases ')

 #  Gets all database names 
 data = cursor.fetchall()

 #  Start operation 
 for dbb in data:
  dbname = dbb[0]
  print(' The selected ' + dbname + ' The database ')
  #  Select database 
  cursor.execute("use " + dbname)
  #  See which tables are available 
  cursor.execute("show tables")
  table = cursor.fetchall()
  #  If it is not 3 I don't care about a watch 
  if len(table) != 3:
   continue
  for tb in table:
   tbname = tb[0]
   print(' To delete '+tbname+' table ')
   #  Delete all tables 
   cursor.execute("DROP TABLE " + tbname)
  executeScriptsFromFile('1.sql', cursor)
 db.close()


def executeScriptsFromFile(filename,cursor):
 fd = open(filename, 'r',encoding='utf-8')
 sqlFile = fd.read()
 fd.close()
 sqlCommands = sqlFile.split(';')

 for command in sqlCommands:
  try:
   cursor.execute(command)
  except Exception as msg:
   print(msg)

 print('sql completes ')


if __name__ == "__main__":
 connectMySQL()

Explain the code

This is used to execute the sql file, there is a pit in the first sentence, it is best to set encoding='utf-8' Otherwise you might get an error UnicodeEncodeError: 'latin-1' codec can't encode characters in position 41-44: ordinal not in range(256) , after reading the sql file; The sql statement is then executed in sequence using the for loop

def executeScriptsFromFile(filename,cursor):
 fd = open(filename, 'r',encoding='utf-8')
 sqlFile = fd.read()
 fd.close()
 sqlCommands = sqlFile.split(';')

 for command in sqlCommands:
  try:
   cursor.execute(command)
  except Exception as msg:
   print(msg)
 print('sql completes ')

This section is easier to understand, the first is to connect to the database, notice that it is better to set 1 charset='utf8' Execute the sql file, because I want to operate more than one database, the database name first so all get out, get out of here is the result of the tuple type, even after for cycle is 1 yuan group, with [0] so take out the values in the tuple, then select the database to perform delete table operation (of course not everyone according to these operations I ah, what you need to perform operations it ourselves SQL statement), finished table delete, and then began to perform 1. sql file, perform closed after the completion of the database

def connectMySQL():
 print(' Start connecting to the database ')
 #  Open database connection 
 db = pymysql.connect(host,username,password,charset='utf8')

 #  use  cursor()  Method to create 1 A cursor object  cursor
 cursor = db.cursor()

 #  use  execute()  Display all databases 
 cursor.execute("SHOW DATABASES")
 print(' Start querying all databases ')

 #  Gets all database names 
 data = cursor.fetchall()

 #  Start operation 
 for dbb in data:
  dbname = dbb[0]
  print(' The selected ' + dbname + ' The database ')
  #  Select database 
  cursor.execute("use " + dbname)
  #  See which tables are available 
  cursor.execute("show tables")
  table = cursor.fetchall()
  #  If it is not 3 I don't care about a watch 
  if len(table) != 3:
   continue
  for tb in table:
   tbname = tb[0]
   print(' To delete '+tbname+' table ')
   #  Delete all tables 
   cursor.execute("DROP TABLE " + tbname)
  executeScriptsFromFile('1.sql', cursor)
 db.close()

The code of these 2 articles from thought, write, to test, used 1 afternoon! I'm not very familiar with Python either, so I got a bit of a dent in the middle, but you can definitely see how comfortable Python is as a glue language for these gadgets!

conclusion