OperationalError: (sqlite3.OperationalError) SQL 变量太多，同时使用 SQL 和数据帧 [英] OperationalError: (sqlite3.OperationalError) too many SQL variables, while using SQL with dataframes

问题描述

我有一个如下所示的熊猫数据框.

I have a pandas dataframe as below.

       activity         User_Id  \
0  VIEWED MOVIE  158d292ec18a49   
1  VIEWED MOVIE  158d292ec18a49   
2  VIEWED MOVIE  158d292ec18a49   
3  VIEWED MOVIE  158d292ec18a49   
4  VIEWED MOVIE  158e00978d7a6c   

                                         Media_Title Media_Type User_Rating  
0  20th Asian Athletics Championship-2013 Held At...                     NA  
1                                 Tu Majha Saangaati                     NA  
2                                       Home Cooking                     NA  
3                                         Mix Dil Se                     NA  
4                  Value, Virtues, Ethics & Morality                     NA

我正在尝试使用如下所示的 pandasql 的 sqldf 包编写 SQL 查询.

I am trying to write an SQL query using sqldf package of pandasql as below.

distinct_activity_user = pandasql.sqldf(" select User_Id from pmm_activity", locals())

我得到的错误是:

OperationalError: (sqlite3.OperationalError) too many SQL variables [SQL: 'INSERT INTO pmm_activity (activity, "User_Id", "Media_Title", "Media_Type", "User_Rating") VALUES

推荐答案

这可能是与列名中的空格相关的问题.当我尝试使用您提供的数据时，我经历了这一点.我有一个使用 sqlite3 的例子.以下示例可能可以解决您的问题:

It might be a problem related to blank spaces in you column names. I experienced that when I tried to work with the data you provided. I have an example using sqlite3. Here is an example that might be a solution to your problem:

import sqlite3 as sql
import pandas as pd

file         = "..../movie.csv"
df = pd.read_csv(file, sep=";", dtype='unicode' )

这是数据帧的样子

conn = sql.connect('movie2.db')
df.to_sql('movie', conn)
conn = sql.connect('movie2.db')
Movie = pd.read_sql('SELECT distinct "User_Id  "  FROM movie', conn)

这篇关于OperationalError: (sqlite3.OperationalError) SQL 变量太多，同时使用 SQL 和数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！