sample for chatting

chat.py

"""

setup:
  pip3 install PyMySQL
"""

import sys
import pymysql.cursors

def printPrompt():
  sys.stdout.write('>>')
  sys.stdout.flush()


def getConnection():
  # Connect to the database
  connection = pymysql.connect(host='localhost',
                              user='',
                              password='',
                              db='',
                              charset='utf8mb4',
                              cursorclass=pymysql.cursors.DictCursor)
  return connection

def backupData(commands):
  tableName = commands[0]
  print ("table=" + tableName)

  connection = getConnection()

  result = None
  with connection.cursor() as cursor:
    sql = "SELECT TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like %s"
    cursor.execute(sql, (tableName,))
    result = cursor.fetchone()
    if result == None:
      print("no such table")
      return
  
  schemaName = result["TABLE_SCHEMA"]
  print(schemaName, tableName)

  columnNames = []
  with connection.cursor() as cursor:
    sql = "SELECT COLUMN_NAME FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = %s AND `TABLE_NAME` = %s"
    cursor.execute(sql, (schemaName, tableName,))
    columns = cursor.fetchall()
    if result == None:
      print("no column")
      return

    for row in columns:
      columnNames.append(str(row["COLUMN_NAME"]))
  
  print(columnNames)

  with connection.cursor() as cursor:
    sql = "SELECT * FROM `" + schemaName + "`.`" +  tableName + "` LIMIT 5"
    cursor.execute(sql, ())
    result = cursor.fetchall()
    if result is None:
      print("no such table")
  
  backupfile = open('backup.sql', 'w')
  insertSql = "insert into `" + tableName + "` (" + ",".join(["`%s`" % s for s in columnNames]) + ") values "
  for row in result:
    sql = insertSql
    sql += "("
    isFirst = True
    for col in columnNames:
      if isFirst:
        # TODO it is not onlye String
        sql += "'" + str(row[col]) + "'"
        isFirst = False
      else:
        sql += ",'" + str(row[col]) + "'"

    sql += ");\n"
    backupfile.write(sql)

  backupfile.close()
  print("done")


def snakeToCamel(snake):
  words = snake.split("_")
  camel = ""
  for i in range(len(words)):
    if i == 0:
      camel += words[i]
    else:
      word = words[i]
      camel += word[0].upper()
      camel += word[1:]
  return camel

def createEntity(commands):
  tableName = commands[0]
  print ("table=" + tableName)

  connection = getConnection()

  with connection.cursor() as cursor:
    sql = "SELECT TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like %s"
    cursor.execute(sql, (tableName,))
    result = cursor.fetchone()
    if result == None:
      print("no such table")
      return
  
  schemaName = result["TABLE_SCHEMA"]
  print(schemaName, tableName)

  columns = []
  with connection.cursor() as cursor:
    sql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_KEY FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = %s AND `TABLE_NAME` = %s"
    cursor.execute(sql, (schemaName, tableName,))
    columns = cursor.fetchall()
    if result == None:
      print("no column")
      return


  entityFile = open('entity.java', 'w')
  camelTableName = snakeToCamel(tableName)
  entityFile.write("\n\nimport javax.persistence.Column;\nimport javax.persistence.Entity;\nimport javax.persistence.GeneratedValue;\nimport javax.persistence.GenerationType;\nimport javax.persistence.Id;\nimport javax.persistence.Table;\nimport java.time.LocalDate;\nimport java.time.LocalDateTime;\n\n")
  entityFile.write("@Entity\n@Table(name = \"" + tableName + "\")\n")
  entityFile.write("public class " + camelTableName[0].upper() + camelTableName[1:] + "Entity {\n")

  properties = ""
  getset = ""
  for column in columns:
    colName = column["COLUMN_NAME"]
    dataType = column["DATA_TYPE"]
    key = column["COLUMN_KEY"]
    camel = snakeToCamel(colName)
    type = "String"
    if dataType == "int" or dataType == "bigint":
      type = "Long"
    elif dataType == "smallint" or dataType == "tinyint":
      type = "Integer"
    elif dataType == "float" or dataType == "double":
      type = "Double"
    elif dataType == "varchar" or dataType == "text":
      type = "String"
    elif dataType == "date":
      type = "LocalDate"
    elif dataType == "datetime":
      type = "LocalDateTime"
    
    properties += "  private " + type + " " + camel + ";\n"
    
    getset += "  public void set" + camel[0].upper() + camel[1:] + "(" + type + " " + camel + ") {\n"
    getset += "    this." + camel + " = " + camel + ";\n"
    getset += "  }\n\n"
    if key == "PRI":
      getset += "  @Id\n"

    getset += "  @Column(name = \"" + colName + "\")\n"
    getset += "  public " + type + " get" + camel[0].upper() + camel[1:] + "() {\n"
    getset += "    return " + camel + ";\n"
    getset += "  }\n\n"

  entityFile.write(properties + "\n")
  entityFile.write(getset + "\n")

  entityFile.write("}\n")
  entityFile.close()
  print("done")

print("This is AI")
printPrompt()
for command in sys.stdin:
  command = command.strip()
  if len(command) > 0:
    commands = command.split(" ")
    if (len(commands) > 1):
      if commands[0] == "backup":
        backupData(commands[1:])

      if commands[0] == "create" and commands[1] == "entity":
        createEntity(commands[2:])
  printPrompt()

print("This is AI")
printPrompt()
for command in sys.stdin:
  command = command.strip()
  if len(command) > 0:
    commands = command.split(" ")
    if (len(commands) > 1):
      if commands[0] == "backup":
        backupData(commands[1:])

      if commands[0] == "create" and commands[1] == "entity":
        createEntity(commands[2:])
  printPrompt()

print("\nbye")

setup.md

Install

PyMySQL

pip3 install PyMySQL