MUstar

IoT Python3 1121 - MySQL Server Tester

Nov 21st, 2017
134
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #_*_ coding: utf-8 _*_
  2. import sys,os
  3.  
  4. #Check Python Version
  5. try:
  6.     assert sys.version_info >= (3,0)
  7. except AssertionError:
  8.     print(sys.version_info)
  9.     print('App>이프로그램은 Python3이상 또는 동급버전에서 실행하야합니다.')
  10.     sys.exit(1)
  11.    
  12. #Import PyMySQL. If not install PyPySQL, installed automatically
  13. try:
  14.     import pymysql
  15. except ImportError:
  16.     print("App>프로그램구동에 필요한 PyMySQL 모듈을 찾을수 없습니다.")
  17.     print("App>PyMySQL모듈을 자동으로 설치합니다.")
  18.     os.system("pip3 install pymysql")
  19.     try:
  20.         import pymysql
  21.         print("App>MyMySQL묘듈 설치완료")
  22.     except ImportError:
  23.         print("App>MyMySQL묘듈 설치실패")
  24.         sys.exit(1)
  25.  
  26. #Global Variable
  27. serverip = None
  28. username = None
  29. password = None
  30. database = None
  31. conn = None
  32. cur = None
  33.  
  34. #MariaDB Connect
  35. def connect():
  36.     global serverip, username, password, conn, cur
  37.     serverip = input("ServerIP>")   #Enter MariaDB Server IP Address
  38.     username = input("username>")   #Enter MariaDB Username
  39.     password = input("password>")   #Enter MariaDB Password
  40.     try:
  41.         conn = pymysql.connect(host=serverip,user=username, password=password, db='pytest', charset = 'utf8mb4',autocommit=True)
  42.         cur = conn.cursor()
  43.     except pymysql.Error as err:    #If MySQL Connect Error
  44.             errno,errinfo = err.args
  45.             if errno==1049:         #If Just no DB, Why not connected.
  46.                 print("App>DB에는 접속되었지만 pytest라는 데이터베이스가 없습니다.")
  47.                 print("App>데이터베이스를 자동으로 생성합니다.")
  48.                 try:
  49.                     conn = pymysql.connect(host=serverip,user=username, password=password,charset = 'utf8mb4',autocommit=True)
  50.                     cur = conn.cursor()
  51.                     cur.execute("create database pytest");
  52.                     cur.execute("use pytest")
  53.                     print("App>테이블 pytable을 생성하고 있습니다.")
  54.                     cur.execute("create table pytable(id int(5) not null auto_increment,name varchar(30) not null, info varchar(100) not null, primary key (id));")
  55.                     cur.execute("alter table pytable convert to character set utf8mb4")
  56.                     print("App>데이터베이스 생성을 성공했습니다.")
  57.                 except pymysql.Error as err:
  58.                     print(err)
  59.                     print("App>데이터베이스 생성을 실패했습니다.")
  60.                     sys.exit(1)
  61.                 print("App>데이터베이스 생성이 완료되었습니다.")
  62.             else:
  63.                 print(err)
  64.                 print("App>DB서버연결에 실패했습니다.")
  65.                 sys.exit(1)
  66.        
  67. def input_value(): # Input Value
  68.     global cur
  69.     _id = "" #input("idvl>")
  70.     name = input("name>")
  71.     info = input("info>")
  72.     query = "insert into pytable (id,name,info) value('NULL','"  +name+ "','" +info+ "');"
  73.     cur.execute(query)
  74.    
  75. def print_row(): # Print Tables
  76.     global cur
  77.     cur.execute("select * from pytable");
  78.     row = cur.fetchall()
  79.     print(row)
  80.    
  81. connect()
  82. print("App>",serverip ,"에 연결 성공")
  83.  
  84. select = None
  85.  
  86. while True:
  87.     print("\n\nMenu>1)Input 2>Print 3>Exit")
  88.     select = input("select>")
  89.  
  90.     if select == '' : print("App>Invalid Value")
  91.     elif select == '1':
  92.         input_value()
  93.     elif select == '2':
  94.         print_row()
  95.     elif select == '3':
  96.         break
  97.     else : print("App>Invalid Value")
  98. conn.close()
  99. cur.close()
  100. print("App>byebye")
RAW Paste Data