I tried to create a database and add data to it with the following code:
import numpy as np
import pandas as pd
import sqlite3 as sqlite3
qb = pd.read_excel('d:/2021_College_QB_Week_3.xlsx', sheet_name = '2021_College_QB_Week_3', header = 0)
print(qb.head)
db_conn = sqlite3.connect("d:/2021_College_Stats2.db")
c = db_conn.cursor()
c.execute(
"""
CREATE TABLE qb(
AP RANK INTEGER,
NAME TEXT NOT NULL,
GAME INTEGER,
CMP INTEGER,
ATT INTEGER,
PCT FLOAT,
YDS INTEGER,
YA FLOAT,
AYA FLOAT,
TD INTEGER,
INT INTEGER,
RATE FLOAT,
SCHOOL TEXT NON NULL,
YEAR INTEGER,
PRIMARY KEY(SCHOOL),
FOREIGN KEY(NAME)REFERENCES qb(NAME)
);
"""
)
qb.to_sql('qb', db_conn, if_exists='append', index=False)
I got this error message and have not seen it before:
[41 rows x 14 columns]>
D:\Anaconda\lib\site-packages\pandas\core\generic.py:2779: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.
sql.to_sql(
Traceback (most recent call last):
File "C:\Users\torou\.spyder-py3\temp.py", line 39, in <module>
qb.to_sql('qb', db_conn, if_exists='append', index=False)
File "D:\Anaconda\lib\site-packages\pandas\core\generic.py", line 2779, in to_sql
sql.to_sql(
File "D:\Anaconda\lib\site-packages\pandas\io\sql.py", line 601, in to_sql
pandas_sql.to_sql(
File "D:\Anaconda\lib\site-packages\pandas\io\sql.py", line 1872, in to_sql
table.insert(chunksize, method)
File "D:\Anaconda\lib\site-packages\pandas\io\sql.py", line 845, in insert
exec_insert(conn, keys, chunk_iter)
File "D:\Anaconda\lib\site-packages\pandas\io\sql.py", line 1595, in _execute_insert
conn.executemany(self.insert_statement(num_rows=1), data_list)
OperationalError: table qb has no column named Unnamed: 0
How do I fix this? It seems my issue is extra columns in the dataframe that are not titled. Is that correct? I am not sure where to start on this one.
-Tim
