Python连接MySQL进行DML操作

1、安装PyMySQL

本地：pip install pymysql

Anaconda:conda install pymysql

2、插入数据

# coding=gbk
import pymysql
from pymysql.cursors import DictCursor

def insert_data():    #插入数据
    course_id = int(input('编号：'))
    course_name = input('名字:')
    #1、创建数据库连接对象
    con = pymysql.connect(host= 'localhost',port=3306
                          ,database='test',charset= 'utf8'
                          ,user='root',password='root')
    try:
        #通过连接对象获取游标
        with con.cursor() as cursor:
            #3、通过游标执行sql并获得执行结果
            result = cursor.execute(
                'insert into course values(%s,%s) ',(course_id,course_name))
            if result ==1:
                print('添加成功!')
            #操作成功提交事务
            con.commit()
    finally:
        #5、关闭连接释放资源
        con.close()
if __name__ == '__main__':
    insert_data()

3、删除数据

def delete_data():   #删除数据
    no = int(input('编号:'))
    con = pymysql.connect(host='134.96.13.12',port=3306
                          ,database='isc_ibrain',charset='utf8'
                          ,user='root',password='root'
                          ,autocommit=True)

    try:
        with con.cursor() as cursor:
            result = cursor.execute(
                'delete from tes_pro where aa=%s',(no,)
            )
            if result == 1:
                print('删除成功')
    finally:
        con.close()

4、更新数据

#更新姓名和时间
def update_data():
    id = int(input('学号：'))
    name =input('姓名：')
    add_time = input('时间:')
    con = pymysql.connect(host= 'localhost',port=3306
                          ,database='test',charset= 'utf8'
                          ,user='root',password='root'
                          ,autocommit=True)
    try:
        with con.cursor() as cursor:
            result = cursor.execute(
                'update users set name = %s ,add_time=%s where id = %s',(name,add_time,id)
            )
            if result == 1:
                print('更新成功')
    finally:
        con.close()

 

5、查询数据

# coding=gbk
import pymysql
from pymysql.cursors import DictCursor

def select_data(): #查询数据并展示
    con = pymysql.connect(host='134.96.13.12', port=3306
                          , database='isc_ibrain', charset='utf8'
                          , user='root', password='root'
                          , autocommit=True)
    try:
        with con.cursor(cursor=DictCursor) as cursor:
            cursor.execute('select target_id,TARGET_CATEGORY_ID,TARGET_NAME,TARGET_DESCRIBE from isc_target')
            results = cursor.fetchall()
            print('场景ID\t\t\t场景关联ID\t\t\t场景名称\t\t\t场景描述')
            for dept in results:
                print(dept['target_id'],end = '\t\t\t')
                print(dept['TARGET_CATEGORY_ID'],end='\t\t\t')
                print(dept['TARGET_NAME'],end='\t\t\t')
                print(dept['TARGET_DESCRIBE'],end ='\t\t\t')
                print('',end = '\n')
    finally:
        con.close()