Python 3-データベース基本操作

1テーブルの作成

import pymysql

config = {
          'host':'localhost',
          'port':3306,
          'user':'root',
          'passwd':'root',
          'db':'test_jeff',
          'charset':'utf8'
          }

try:
    #        
    conn = pymysql.connect(**config)

    #    cursor()            cursor
    cursor = conn.cursor()

    #    execute()      SQL，        
    cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

    #           
    sql = """CREATE TABLE EMPLOYEE (
             FIRST_NAME  CHAR(20) NOT NULL,
             LAST_NAME  CHAR(20),
             AGE INT,  
             SEX CHAR(1),
             INCOME FLOAT,
             PRIMARY KEY (FIRST_NAME)  
             )"""

    cursor.execute(sql)
    #     
    cursor.close()
    conn.close()
    print("    ")
except Exception :print("      ")

2データの挿入

import pymysql

config = {
          'host':'localhost',
          'port':3306,
          'user':'root',
          'passwd':'root',
          'db':'test_jeff',
          'charset':'utf8'
          }
#        
conn = pymysql.connect(**config)

try:
    #    cursor()            cursor
    cursor = conn.cursor()

    # SQL     
    sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
             LAST_NAME, AGE, SEX, INCOME)
             VALUES ('jiacheng', 'li', 26, 'M', 800)"""

    #   sql  
    cursor.execute(sql)

    # commit   
    conn.commit()

    #     
    cursor.close()

    #     
    conn.close()
    print("    ")
except:
    print("      ")

    #        
    conn.rollback()

3クエリーデータ

import pymysql

config = {
          'host':'localhost',
          'port':3306,
          'user':'root',
          'passwd':'root',
          'db':'test_jeff',
          'charset':'utf8'
          }
#        
conn = pymysql.connect(**config)

try:
    #    cursor()            cursor
    cursor = conn.cursor()

    # SQL     
    sql = "SELECT * FROM EMPLOYEE \
           WHERE INCOME > '%d'" % (1000)

    #   SQL  
    cursor.execute(sql)
    #         
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        #     
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
              (fname, lname, age, sex, income))

    #     
    cursor.close()

    #     
    conn.close()
    print("    ")
except:
    print("    ")
    #        

4データの変更

import pymysql

config = {
          'host':'localhost',
          'port':3306,
          'user':'root',
          'passwd':'root',
          'db':'test_jeff',
          'charset':'utf8'
          }
#        
conn = pymysql.connect(**config)

try:
    #    cursor()            cursor
    cursor = conn.cursor()

    # SQL     
    sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 " \
          "WHERE SEX = '%c'" % ('M')

    #   SQL  
    cursor.execute(sql)

    #     
    conn.commit()

    #     
    cursor.close()

    #     
    conn.close()
    print("    ")
except:
    print("    ")

5データの削除

import pymysql

config = {
          'host':' localhost',
          'port':3306,
          'user':'root',
          'passwd':'root',
          'db':'test_jeff',
          'charset':'utf8'
          }
#        
conn = pymysql.connect(**config)

try:
    #    cursor()            cursor
    cursor = conn.cursor()

    # SQL     
    sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (25)

    #   SQL  
    cursor.execute(sql)

    #     
    conn.commit()

    #     
    cursor.close()

    #     
    conn.close()
    print("    ")
except:
    print("    ")