分析无主键表再做优化

背景

新接手一项目，需要优化表，首先表中存在一些无主键的表，不确认是否用到了自增id做主键。
需求：
1.把id是主键的表字段存一文件中 这部分表不需要改动，只是记录就好，但如果只是id为主键但不bigint的后续也需要改。
2.把存在主键 但不是id的表字段单独存在一文件中，后续需要做优化。
3.没有主键的也存在一文件中，这部分优先处理。

思路：
首先想到的是information_schema.tables 和 columns关联 可以查出第一列信息（一般主键id都放在第一列 也存在例外）第一步先把表中的第一列取出 判断是否为主键，并记录信息 其它情况分别处理。

import pymysql

FIRST_COL_SQL = "select t.table_name,c.column_name,c.data_type,c.COLUMN_TYPE,c.column_key from  information_schema.tables t join information_schema.columns c  on t.table_name=c.table_name where t.table_schema ='db_name' and t.table_type like '%table%' and ORDINAL_POSITION=1 order by table_name"
FULL_COL_SQL  = "select table_name,column_name,data_type,COLUMN_TYPE,column_key from information_schema.columns where table_schema ='db_name' and table_name ='{}' order by table_name,column_name "

def processColInfo(hostname,uname,pword,db_name):
  #find first column of table  id common  if not id then use full_col_sql search exists id column 
  #exists then check if primary key or add primary key
  #                       0                                1                     2           3                4
  #first column res  | table_name                    | column_name         | data_type | COLUMN_TYPE    | column_key |
  justrecord = "/root/pyscript/justrecord.txt"
  fcr = executeSql(hostname,uname,pword,db_name,FIRST_COL_SQL)
  for lfcr in fcr:
    if lfcr[1].strip().lower().startswith('id'):
      if lfcr[4].strip().lower() == 'pri':
        # id is primary key write file of no_change 
        jrecord = "{:50} primary key is {:10}  type is {:30}\n".format(lfcr[0],lfcr[1],lfcr[3])
        writeFile(jrecord,justrecord)
      else:
        #second times check if has pri   
        processColNoPri(hostname,uname,pword,db_name,lfcr[0],True)
    # no id column then check if has pri  
    else: 
      processColNoPri(hostname,uname,pword,db_name,lfcr[0],False)

针对表中第一列不是id的表，查询其字段，并确认是否存在主键，有或没有分别记录。

def processColNoPri(hostname,uname,pword,db_name,table_name,firstcol_id):
  hasprimarykey = "/root/pyscript/hasprimary.txt"
  hasprimarykeynoid = "/root/pyscript/hasprimarynotid.txt"
  nopritable = "/root/pyscript/noprimary.txt"
  # table column res 
  print(table_name)
  fullcolsql = FULL_COL_SQL.format(table_name)
  tcr = executeSql(hostname,uname,pword,db_name,fullcolsql)
  loopcnt = 0

  while loopcnt < len(tcr):
    #print(table_name)
    #check table if has id column
    if not firstcol_id:
      if tcr[loopcnt][0].strip().lower().startswith('id'):
        firstcol_id = True
    #has pri 
    if tcr[loopcnt][0] is not None and tcr[loopcnt][0].strip() != "" and tcr[loopcnt][4].strip().lower() == 'pri':
      if firstcol_id:
        sqlstr = "###table {} primary key  {}  datatype {}\n".format(tcr[loopcnt][0],tcr[loopcnt][1],tcr[loopcnt][3])
        writeFile(sqlstr,hasprimarykey)
      else:
        sqlstr = "$$$table {} primary key  {}  datatype {} \n".format(tcr[loopcnt][0],tcr[loopcnt][1],tcr[loopcnt][3])
        writeFile(sqlstr,hasprimarykeynoid)
      break; 
    loopcnt +=1
  else:
  #no primary key
    nopri = "{} no id and no primary key \n".format(tcr[0][0])
    writeFile(nopri,nopritable)

连接数据库执行sql

#execute from mysql
def executeSql(hostname,uname,pword,db_name,sql):

  conn = pymysql.connect(host=hostname, port=31108, user=uname, password=pword)
  cursor = conn.cursor()
  changedb = 'use `{}`'.format(db_name)
  cursor.execute( changedb )

  #selsql

  cursor.execute(sql)
  res=[]
  try:
    res = cursor.fetchall()
  except pymysql.Error as err:
    print(err)
  finally:
    cursor.close()
    conn.close()
  return res

记录log

#write column info  to file 
def writeFile(info,filename='/root/col_log'):
  with open(filename,'a+') as f:
    f.write(info) 

if __name__ == '__main__':
  host = 'ip'
  user = 'user'
  pword = 'pass'
  db_name = 'dbname'
  processColInfo(host,user,pword,db_name)