SQLite3 3表联接，列不存在？

Question

当我执行查询时

    /* 1st Join: sec_table and wrds */
    SELECT *
    FROM   sec_table
    JOIN   wrds
    ON     sec_table.cik == wrds.cik
    and    file_date between LINKDT and LINKENDDT

    /* 2nd Join:  _______ and sdc */
    JOIN   sdc
    ON     substr(wrds.cusip,0,7) == sdc.cusip_acquiror
    and    sdc.dt_announced between wrds.LINKDT and wrds.LINKENDDT

我得到的错误是，尽管sdc.dt_announced是sdc中的列，但它并不存在。

复制错误的代码

import io
import pandas as pd
import sqlite3

sec_table = pd.read_csv(
    io.StringIO(
    """
        page,href,file_name,file_date,company_name,cik
        1,/Document/0001019056-14-000922/,0001019056-14-000922,2014-07-16,"mktg, inc.",0000886475
        1,/Document/0001104659-07-059707/,0001104659-07-059707,2007-08-07,1 800 CONTACTS INC,0001050122
    """)
)
wrds = pd.read_csv(
    io.StringIO(
    """
        GVKEY,LINKDT,LINKENDDT,iid,tic,cusip,conm,cik,conml,naics,sic
        1004,1972-04-24,2020-04-22,1,AIR,000361105,AAR CORP,0000001750,AAR Corp,423860.0,5080
        1009,1982-01-18,1996-03-13,1,ABSI,000781104,ABS INDUSTRIES INC,0000313368,ABS Industries Inc,3321.0,3460

    """)
)
sdc = pd.read_csv(
    io.StringIO(
    """
        dt_announced,result,cusip_acquiror,cusip_target
        1995-01-01,Completed,31687H,03238Y
        1995-01-01,Completed,N9643A,03882N

    """)
)

conn = sqlite3.connect(":memory:")

sec_table.to_sql('sec_table', conn, index=False)
sdc.to_sql('sdc', conn, index=False)
wrds.to_sql('wrds',conn, index=False)

qry = '''
    /* 1st Join: sec_table and wrds */
    SELECT *
    FROM   sec_table
    JOIN   wrds
    ON     sec_table.cik == wrds.cik
    and    file_date between LINKDT and LINKENDDT

    /* 2nd Join:  _______ and sdc */
    JOIN   sdc
    ON     substr(wrds.cusip,0,7) == sdc.cusip_acquiror
    and    sdc.dt_announced between wrds.LINKDT and wrds.LINKENDDT 
'''

pd.read_sql_query(qry, conn)

no such column: sdc.dt_announce

Answer 1

如果您像使用print(sdc.columns)一样查看列名，您将看到

['        dt_announced', 'result', 'cusip_acquiror', 'cusip_target']

类似地，其他表在第一个列名中有前导空格。

(注意:当我运行这个脚本时，我得到了"UserWarning:这些列名中的空格不会被更改。在熊猫版本中，空格被转换为下划线“)。

一种解决方案是为每个数据帧添加{DataFrame}.columns = {DataFrame}.columns.str.strip()。

Answer 2

在使用熊猫阅读数据时，你面临的问题是空白字符。有多种方法可以解决这个问题，例如

1. 在SQLite

中创建视图/表之前先清除列名中的空白字符。

df.columns = df.columns.str.replace(" ", "")

如果

1. 的SQLite大于3.25.0，则可以为这些列使用别名。确保空格包含在‘’(单引号)

中

# Identify the columns having whitesapce
cursor = con.cursor()
colInfo = cursor.execute("PRAGMA table_info('tableName')")
for r in colInfo:
    print("Columns Names :: " + str(r))

# The above part will print each column name with hidden white-spaces, 
# include the same while aliasing the column name as below.
rsDf = pd.read_sql_query(
    "select ' colNameAlongWithWhiteSpace' as temp  from tableName ", con,
)

print(rsDf.columns)

1. 如果您的SQLite在3.25.0之后，那么使用alter语句更改列名.

# PySQLite version 
print(sqlite3.version)

# SQLite Version, this value should be greater than 3.25.0
print(sqlite3.sqlite_version)

cursor = con.cursor()
cursor.execute("ALTER TABLE <tableName> RENAME COLUMN ' columnWithWhitespace'TO <New ColumnName> ")

有关SQLite https://www.sqlite.org/lang_altertable.html的ALTER命令的更多信息