python 程序对 mysql操作

插入数据：

from pymysql import *

def insert_mysql():
    try:
        # 创建连接对象
        conn = connect(host='192.168.20.82',user='root',password='mysql',
                       database='python',port=3306,charset='utf8',
                       )
        # 获取cursor对象
        cur = conn.cursor()
        try:
            sql = 'insert into students VALUES (0,"露娜",1,"深圳",2,19);'
            #执行sql语句
            res = cur.execute(sql)
            # 打印受影响的行数
            print(res)
            cur.close() # 关闭cursor对象
            conn.commit() # 提交事务：todo：涉及到插入，删除，修改，更新操作都要commit
        except Exception as e:
            print(e)
        finally:
            # 如果中间执行有错误，导致不能关闭连接对象
            # 所以讲连接对象放到finally语句里
            conn.close()
    except Exception as e:
        print(e)

if __name__ == '__main__':
    insert_mysql()

查询操作

from pymysql import *

def insert_mysql():
    try:
        # 创建连接对象
        conn = connect(host='192.168.20.82',user='root',password='mysql',
                       database='python',port=3306,charset='utf8',
                       )
        # 获取cursor对象
        cur = conn.cursor()
        try:
            sql = 'select * from students where id=15;'
            #执行sql语句
            cur.execute(sql)
            res = cur.fetchone()  # 获取查询集的第一条数据
            # res = cur.fetchall()  # 获取查询集所有数据
            print(res)
        except Exception as e:
            print(e)
        finally:
            # 如果中间执行有错误，导致不能关闭连接对象
            # 所以讲连接对象放到finally语句里
            cur.close()
            conn.close()
    except Exception as e:
        print(e)

if __name__ == '__main__':
    insert_mysql()

执行sql语句参数化

参数化sql语句中使用%s占位。

execute(operation [parameters]) 执行语句，返回受影响的行数，可以执行所有语句

[parameters] 参数列表

from pymysql import *

def insert_mysql():
    try:
        # 创建连接对象
        conn = connect(host='192.168.20.82',user='root',password='mysql',
                       database='python',port=3306,charset='utf8',
                       )
        # 获取cursor对象
        cur = conn.cursor()
        try:
            sql = 'select * from students where id=%s and gender= %s;' # sql语句中使用%s占位
            #执行sql语句
            cur.execute(sql,[15,0])
            # res = cur.fetchone()  # 获取查询集的第一条数据
            res = cur.fetchall()  # 获取查询集所有数据
            print(res)
            for i in res:  # 将每一条数据打印出来
                print(i)
        except Exception as e:
            print(e)
        finally:
            # 如果中间执行有错误，导致不能关闭连接对象
            # 所以讲连接对象放到finally语句里
            cur.close()
            conn.close()
    except Exception as e:
        print(e)

if __name__ == '__main__':
    insert_mysql()