Python使用技巧之MySQL数据库增删改查操作

水煮麥楽雞

发布于 2022-11-20 15:12:33

1.8K0

发布于 2022-11-20 15:12:33

连接数据库

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

插入操作

    import pymysql
    
    def get_conn():
        conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')
        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_table VALUES(1,\'dzy\',12);''
        insert(sql)

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

sql占位符形式实现

    import pymysql
    
    def get_conn():
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test')
        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_table VALUES(%s,%s,%s);'
        insert(sql, (2, 'dzy', 13))

插入多条语句实现

    import pymysql
    
    
    def get_conn():
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test')
        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_table VALUES (%s,%s,%s)'
        args = [(3, 'd', 11), (4, 'z', 12), (5, 'y', 13)]
        insert_many(sql=sql, args=args)

更新操作

    import pymysql
    
    def get_conn():
        conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')
        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_table SET NAME=%s WHERE id = %s;'
        args = ('dzy', 1)
        update(sql, args)

删除操作

    import pymysql
    
    def get_conn():
        conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')  
        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_table WHERE id = %s;'
        args = (1,) # 单个元素的tuple写法
        delete(sql,args)

查询操作

    import pymysql
    
    def get_conn():
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test')
        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_table;'
        query(sql,None)

版权属于：。。。源
本文链接：https://cloud.tencent.com/developer/article/2169629
转载时须注明出处及本声明。我的博客即将同步至腾讯云开发者社区，邀请大家一同入驻：https://cloud.tencent.com/developer/support-plan?invite_code=2py75w7904qok

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2021-06-15，如有侵权请联系 cloudcommunity@tencent.com 删除

sql