Python之MySQL数据库增删改查操作

Python之MySQL数据库操作

Python之连接数据库

import pymysql

# 获取连接对象conn，建立数据库的连接
def get_conn():
    conn = pymysql.connect(host=localhost,port=3306,user=root,passwd=root,db=test1)    # db:表示数据库名称
    return conn

Python之插入操作

import pymysql

def get_conn():
    conn = pymysql.connect(host=localhost,port=3306,user=root,passwd=root,db=test1)    # db:表示数据库名称
    return conn

def insert(sql):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql)
    print(result)
    conn.commit()
    cur.close()
    conn.close()

if __name__ == __main__:
    sql = INSERT INTO test_student_table VALUES(1,zhang,12);
    insert(sql)

在写sql语句的时候，需要避免直接写sql语句，而是采用占位符的方式来，防止sql的注入。

sql占位符形式实现

import pymysql

def get_conn():
    conn = pymysql.connect(host=localhost, port=3306, user=root, passwd=root, db=test1)
    return conn

def insert(sql, args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql, args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()



if __name__ == __main__:
    sql = INSERT INTO test_student_table VALUES(%s,%s,%s);
    insert(sql, (2, wang, 13))

下面代码均以占位符的形式实现sql语句。

插入多条语句实现

import pymysql


def get_conn():
    conn = pymysql.connect(host=localhost, port=3306, user=root, passwd=root, db=test1)
    return conn

def insert_many(sql, args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.executemany(query=sql, args=args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()


if __name__ == __main__:
    sql = insert into test_student_table VALUES (%s,%s,%s)
    args = [(3, li, 11), (4, sun, 12), (5, zhao, 13)]
    insert_many(sql=sql, args=args)

Python之更新操作

import pymysql

def get_conn():
    conn = pymysql.connect(host=localhost,port=3306,user=root,passwd=root,db=test1)    # db:表示数据库名称
    return conn

def update(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql,args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()


if __name__ == __main__:
    sql = UPDATE test_student_table SET NAME=%s WHERE id = %s;
    args = (zhangsan, 1)
    update(sql, args)

Python之删除操作

import pymysql

def get_conn():
    conn = pymysql.connect(host=localhost,port=3306,user=root,passwd=root,db=test1)    # db:表示数据库名称
    return conn

def delete(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql,args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()


if __name__ == __main__:
    sql = DELETE FROM test_student_table WHERE id = %s;
    args = (1,) # 单个元素的tuple写法
    delete(sql,args)

Python之查询操作

import pymysql

def get_conn():
    conn = pymysql.connect(host=localhost, port=3306, user=root, passwd=root, db=test1)
    return conn

def query(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(sql,args)
    results = cur.fetchall()
    print(type(results))  # 返回<class tuple> tuple元组类型

    for row in results:
        print(row)
        id = row[0]
        name = row[1]
        age = row[2]
        print(id:  + str(id) +   name:  + name +   age:  + str(age))
        pass

    conn.commit()
    cur.close()
    conn.close()


if __name__ == __main__:
    sql = SELECT  * FROM test_student_table;
    query(sql,None)

输出：

<class tuple>
(2, wang, 13)
id: 2  name: wang  age: 13
(3, li, 11)
id: 3  name: li  age: 11
(4, sun, 12)
id: 4  name: sun  age: 12
(5, zhao, 13)
id: 5  name: zhao  age: 13