python----积累---使用传参的方式构造sql语句

向右看齐 2022-09-04 00:55 48阅读 0赞

使用pymsql查询数据如下：

import pymysql
    
    conn=pymysql.connect(host='123',
                               port=3306,
                               user=root',
                               password='12345',
                               database='school',
                               charset='utf8')
    
    
    
    results = []
    
    
    sql = """
    select * from students where class in ('3班','4班','6班','8班')  and province in  ('北京','广西','四川','云南')
    """
    
    # print(sql)
    
    try:
        cursor = pymysql.cursors.SSCursor(conn)
        result=cursor.execute(sql)
        while True:
            row = cursor.fetchone()
    #         print(row)
            if not row:
                print("finish")
                break
            results.append((row[0],row[1],row[2],str(row[1])+str(row[2])))
    except:
       print("Error: unable to fecth data")

这样的用法sql语句适合 少量的参数变量，但是 当变量值 成千上万个时，就需要 把参数提取出来，通过传参的方式进行构造。

# 方案一 #

import pymysql
    
    conn=pymysql.connect(host='123',
                               port=3306,
                               user=root',
                               password='12345',
                               database='school',
                               charset='utf8')
    
    
    
    class_list=['3班','4班','6班','8班']
    
    province_list=['北京','广西','四川','云南']
    
    results = []
    
    
    sql = """
    select * from students where class in {} and province in {}
    """.format(tuple(class_list),tuple(province_list))
    
    # print(sql)
    
    try:
        cursor = pymysql.cursors.SSCursor(conn)
        result=cursor.execute(sql)
        while True:
            row = cursor.fetchone()
    #         print(row)
            if not row:
                print("finish")
                break
            results.append((row[0],row[1],row[2],str(row[1])+str(row[2])))
    except:
       print("Error: unable to fecth data")

# 方案二 #

select_str = 'select * from server where ip in (%s)' % ','.join(['%s'] * len(a))     
    >>> select_str
    'select * from server where ip in (%s,%s,%s)'
    这里我们先根据Ip列表的长度来生成对应的参数位置，然后通过MySQLdb模块中的execute函数来执行：
    cursor.execute(select_str,a)
    
    
    
    
    import pymysql
    
    conn=pymysql.connect(host='123',
                               port=3306,
                               user=root',
                               password='12345',
                               database='school',
                               charset='utf8')
    
    
    
    class_list=['3班','4班','6班','8班']
    
    province_list=['北京','广西','四川','云南']
    
    results = []
    
    
    sql = 'select * from students where class in   (%s) and province in   (%s) ' % ( ','.join(['%s'] * len(class_list)),  ','.join(['%s'] * len(province_list)) )
    
    # print(sql)
    
    
    
    
    
    try:
        cursor = pymysql.cursors.SSCursor(conn)
        result=cursor.execute(sql,class_list,province_list)
        while True:province_list
            row = cursor.fetchone()
    #         print(row)
            if not row:
                print("finish")
                break
            results.append((row[0],row[1],row[2],str(row[1])+str(row[2])))
    except:
       print("Error: unable to fecth data")