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 pymysqldef get_conn():    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1')    # db:表示数据库名称    return conndef 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 pymysqldef get_conn():    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')    return conndef 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 pymysqldef get_conn():    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')    return conndef 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 pymysqldef get_conn():    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1')    # db:表示数据库名称    return conndef 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 pymysqldef get_conn():    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1')    # db:表示数据库名称    return conndef 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 pymysqldef get_conn():    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')    return conndef 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