如何解决“OperationalError: too many SQL variables”错误

Question

我正在尝试将大小为(8760, 1574)的数据框插入到SQLite表中。我的代码如下：

class DatabaseWorker(object):
    def __init__(self, db_name):
        self.db = db_name

    def create_table(self, table_name, column_names):
        conn = sqlite3.connect(self.db)
        cur = conn.cursor()
        q1 = 'DROP TABLE IF EXISTS %s' %(table_name)
        q2 = 'CREATE TABLE ' + table_name + ' ' + '(' + ', '.join(str(x) for x in column_names) + ')'
        cur.execute(q1)
        cur.execute(q2)
        conn.commit()
        conn.close()

    def insert_table(self, table_name, data):
        conn = sqlite3.connect(self.db)
        data.to_sql(table_name, conn, if_exists='append', index=False)
        conn.commit()
        conn.close()

cnx = DatabaseWorker("users")
cnx.create_table("user_activity", df_final.columns.values.tolist())
cnx.create_table("user_similarity_matrix", df_transformed.columns.values.tolist())
cnx.insert_table("user_activity", df_final)
cnx.insert_table("user_similarity_matrix", df_transformed)

df_final的大小为(249238, 7)，而df_transformed的大小为(8760, 1574)。在插入df_final时没有错误，但是在插入df_transformed时出现了错误。错误如下所示：

----> 5 cnx.insert_table("user_similarity_matrix", df_transformed)

---> 30 data.to_sql(table_name, conn, if_exists='append', index=False)

pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index, index_label=index_label, schema=schema, chunksize=chunksize, dtype=dtype)

OperationalError: too many SQL variables

在"Too many SQL variables" error in django witih sqlite3上的一个答案中提到了变量限制为999个？我能否绕过这个限制呢？感谢您提前的建议。

- Krishnang K Dalal

Answer 1

< p > SQLITE_MAX_VARIABLE_NUMBER 在SQLite > 3.32.0中增加，从999增加到32766。

9. 单个SQL语句中主机参数的最大数量

   [...]

   SQLite分配空间来保存1到使用的最大主机参数号之间的所有主机参数。因此，包含像?1000000000这样的主机参数的SQL语句将需要占用几GB的存储空间。这很容易超出主机机器的资源限制。为了防止过度的内存分配，主机参数号的最大值是SQLITE_MAX_VARIABLE_NUMBER，默认情况下对于SQLite版本3.32.0（2020-05-22）之前的版本为999，对于SQLite版本3.32.0之后的版本为32766。

这意味着您的具有1574列的df_transformed也应该在最新的SQLite版本中正常工作。但是请注意，SQLITE_MAX_COLUMN=2000，您没有太多的空间来增加数据帧中的列数。

默认的SQLITE_MAX_COLUMN设置为2000。您可以在编译时将其更改为最大32767的值。另一方面，许多经验丰富的数据库设计师会认为，在表中，一个规范化良好的数据库永远不需要超过100个列。

在大多数应用程序中，列数很少，只有几十个。在SQLite代码生成器中，有些地方使用O（N²）算法，其中N是列数。

替代方案可以是使用 pandas.DataFrame.to_json / pandas.read_json 在 SQLite 的 TEXT 列中进行自定义文档序列化，pandas.DataFrame sqlite3 适配器 等等。由于 SQLITE_MAX_LENGTH 每行为 1GB，序列化提供了更宽的数据帧。

- saaj

---

谢谢你，saaj。这将在未来非常有帮助。 - Krishnang K Dalal