sqllite根据条件一次查询多条数据

有时候我们需要一个list列表来查询多条数据，来避免多次执行sql语句，下面就通过一个简单的demo来实现这个功能

• 连接数据库

import sqlite3

#连接数据库
conn = sqlite3.connect('test.db')
#获取游标用来执行sql语句
c = conn.cursor()

• 建表
  我们创建一个company表，里面包含了员工的id、name、age、address、salary信息

def create_table():
    c.execute('''CREATE TABLE COMPANY
           (ID INT PRIMARY KEY     NOT NULL,
           NAME           TEXT    NOT NULL,
           AGE            INT     NOT NULL,
           ADDRESS        CHAR(50),
           SALARY         REAL);''')

• 插入数据

def insert_data_to_table():
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (1, 'Paul', 32, 'California', 10000.00 )")
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")

• 查询数据
  通过where.......in来实现根据一个list来查询符合条件的多条数据

def select_data(name_list):
    sql = "select * from COMPANY where NAME in ({seq})".format(
        seq=','.join(['?'] * len(name_list)))
    c.execute(sql, name_list)
    return c.fetchall()
#创建commpany表
#create_table()
#插入数据
# insert_data_to_table()
print(select_data(["Paul","Allen"]))
"""
[(1, 'Paul', 32, 'California', 10000.0), (2, 'Allen', 25, 'Texas', 15000.0)]
"""
conn.commit()
conn.close()

参考:
1.https://stackoverflow.com/questions/5766230/select-from-sqlite-table-where-rowid-in-list-using-python-sqlite3-db-api-2-0
2.https://www.runoob.com/sqlite/sqlite-python.html