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

男娘i 2022-06-07 12:46 226阅读 0赞

### 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)

![这里写图片描述][SouthEast]

在写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))

![这里写图片描述][SouthEast 1]  
下面代码均以占位符的形式实现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)

![image][]

--------------------

#### 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)

![image][image 1]

--------------------

#### 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)

![image][image 2]

--------------------

#### 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

![image][image 3]

[SouthEast]: /images/20220607/37bb1a9dab3f4b65b5a3ad077289f904.png
[SouthEast 1]: /images/20220607/4029587132064003b8e966bd58d8bac4.png
[image]: /images/20220607/206d3bfe4e2245caa9f58f7505f82a13.png
[image 1]: /images/20220607/c4ae187d2a304f9386f83ea4ba9e341d.png
[image 2]: /images/20220607/074b2717986d478c8fb922bf57e6024e.png
[image 3]: /images/20220607/8a120a6980074fe3a4cdff3900677764.png