0

I am using pandas to read in the csv file, since I'm familiar with dataframes from R. But I think that may not be the issue here, which is that db.execute fails to export data to students.db. The code, given below, in a nutshell (i) first reads in the data, (ii) parses the "name" and appends 3 extra columns - "first", "middle" and "last", and (iii) subset the file for export. All this works correctly. Only the export doesn't seem to work - I am getting a runtime error. Would greatly appreciate any pointer on what I'm doing wrong.

import sys, csv, pandas as pd, sqlite3
from cs50 import SQL

infile = pd.read_csv(sys.argv[1])
#print(infile)
names = infile.name.str.split(expand = False)
#print(type(list(names)))
namesList = list(names)
#print(len(namesList))

first = []
middle = []
last = []
for i in range(len(namesList)):
    first.append(namesList[i][0]) # http://projectpython.net/chapter07/
    if len(namesList[i]) == 2:
        middle.append(None)
        last.append(namesList[i][1])
    else:
        middle.append(namesList[i][1])
        last.append(namesList[i][2])
#print(first)

# Appending to infile. See: https://stackoverflow.com/questions/12555323/adding-new-column-to-existing-dataframe-in-python-pandas
infile["first"] = first
infile["middle"] = middle
infile["last"] = last
outfile = infile[['first', 'middle', 'last', 'house', 'birth']]
print(outfile.head())
#print(infile)


db = SQL("sqlite:///students.db")
db.execute("INSERT INTO students VALUES (NULL,?,?,?,?,?)", (outfile["first"], outfile["middle"], outfile["last"], outfile["house"], outfile["birth"]))

Error message

Traceback (most recent call last):
  File "import.py", line 34, in <module>
    db.execute("INSERT INTO students VALUES (NULL,?,?,?,?,?)", (outfile["first"], outfile["middle"], outfile["last"], outfile["house"], outfile["birth"]))
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 21, in decorator
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 181, in execute
    _args = ", ".join([str(self._escape(arg)) for arg in args])
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 181, in <listcomp>
    _args = ", ".join([str(self._escape(arg)) for arg in args])
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 460, in _escape
    return __escape(value)
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 454, in __escape
    raise RuntimeError("unsupported value: {}".format(value))
RuntimeError: unsupported value: 0      Adelaide
1        Adrian
2       Anthony
3        Blaise
4        Cedric
5           Cho
6         Colin
7          Dean
8         Draco
9        Ernest
10      Ginevra
11      Gregory
12       Hannah
13        Harry
14     Hermione
15       Isobel
16       Justin
17     Lavender
18         Lisa
19         Luna
20        Mandy
21       Marcus
22     Marietta
23      Michael
24    Millicent
25      Neville
26        Padma
27        Pansy
28      Parvati
29     Penelope
30       Robert
31        Roger
32      Romilda
33       Ronald
34       Seamus
35        Susan
36      Terence
37        Terry
38       Tracey
39      Vincent
Name: first, dtype: object
1
  • My experience with pandas is very light, but dataframe['col name'] always gives row number. Why not use a sqlite3 connection (instead of CS50) to the database, and pandas Dataframe.to_sql. – DinoCoderSaurus Jun 15 '20 at 20:08

You must log in to answer this question.

Browse other questions tagged .