pymysql 在python中的用法增刪改查

本文主要是写了pymysql的用法,自己记录一下

# coding=utf8

import pymysql
import time
import datetime

'''
启动MySQL
cd /usr/local/mysql     进入MySQL目录
sudo /usr/local/mysql/support-files/mysql.server start      启动MySQL
'''


sync_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
stamp = time.time()  # 获取当前时间戳


# 使用 cursor() 方法创建一个游标对象 cursor
conn = pymysql.connect(
    host="localhost",  # 数据库IP
    port=3306,      # 端口号
    user='root',    # 数据库账号
    passwd='000',      # 数据库登录密码
    db='db_name',
    charset='utf8',
  unix_socket="/tmp/mysql.sock" # 路径
)

def create_sql():
    cursor = conn.cursor()
    # 使用 execute() 方法执行 SQL，如果表存在则删除
    # cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
    cursor.execute("drop table if exists EMPLOYEE")
    # 使用预处理语句创建表 create table
    sql = """create table EMPLOYEE (
             FIRST_NAME  CHAR(20) NOT NULL,
             LAST_NAME  CHAR(20),
             AGE INT,
             SEX CHAR(1),
             INCOME FLOAT,
             sync_date datetime
             )"""
    cursor.execute(sql)

def add_sql():
    # 增加数据库字段
    cursor = conn.cursor()
    sql = "alter table EMPLOYEE add sync varchar(100);"
    cursor.execute(sql)


def insert_into():
	# 插入数据库字段
    cursor = conn.cursor()
    sql = "INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME, sync_date)" \
          " VALUES ('{}', '{}',  {},  '{}',  {} ,'{}')".format('Mac2', 'haode', 20, 'M', 2000 , sync_date)
    # 执行sql语句
    try:
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
        print("数据插入成功")
    except:
        print ("Error: unable to fetch data")
    conn.close()


def select():
 	# 查询数据库字段
    cursor = conn.cursor()
    # SQL 查询语句
    sql = "SELECT * FROM EMPLOYEE "
    try:
       # 执行SQL语句
       cursor.execute(sql)
       # 获取所有记录列表
       results = cursor.fetchall()
       for i in results:
           print(i[5])
           print(list(i))
            # res = results[i]
            # print(res)
            # for j in res:
            #     print(res[5])
    except:
       print ("Error: unable to fetch data")
    # 关闭数据库连接
    conn.close()


def delete():
	# 删除数据库字段
    curses = conn.cursor()
    sql = "DELETE FROM EMPLOYEE WHERE FIRST_NAME = 'Mac3'"
    try:
        curses.execute(sql)
        conn.commit()
        print("数据删除成功")

    except:
        print ("Error: unable to fetch data")
    conn.close()


def update():
	# 修改数据库字段
    curses = conn.cursor()
    sql = "UPDATE EMPLOYEE SET FIRST_NAME='Mac3' , SEX = '男' WHERE LAST_NAME = 'haode' "
    try:
        curses.execute(sql)
        conn.commit()
        print("数据更新成功")

    except:
        print ("Error: unable to fetch data")


if __name__ == '__main__':
    add_sql()
    create_sql()
    insert_into()
    select()
    update()
    select()
    delete()