Python/Sql - Creating a table and loading csv file

Question

I have managed to create a table called drivers however I am unable to load a csv file called drivers.csv into this table

Code

Error:

File "/workspaces/87976355/project/app.py", line 17, in <module>
    db.execute("INSERT INTO drivers (driverId, driverRef, number, code, forename, surname, dob, nationality, url) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", row.split(","))

RuntimeError: more placeholders (?, ?, ?, ?, ?, ?, ?, ?, ?) than values ('driverId', 'driverRef', 'number', 'code', 'forename', 'surname', 'dob', 'nationality', 'url
')

I've also tried to do:

db.execute("INSERT INTO drivers (driverId, driverRef, number, code, forename, surname, dob, nationality, url) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", driverId, driverRef, number, code, forename, surname, dob, nationality, url)

csv file format

But no luck which I believe python is reading driverid, driverref etc. as variables rather than name of the columns in table - drivers.

Would anyone know why I am encountering this?

Please provide your code and the csv file format as text. Markdown Editing Help is available at: stackoverflow.com/editing-help — jboockmann
Welcome to the site. Please do not post pictures of your code. — khelwood
I would just create the table once by hand in the SQL console, and then the Python script only does the inserting data part. Also, you could try the CSV Lint plug-in for Notepad++ github.com/BdR76/CSVLint it can convert csv data into a SQL script with CREATE TABLE and INSERT statements. — BdR

BdR BdR · Accepted Answer · 2022-07-28T10:15:25

I think you're looking for string formatting in Python, which uses a % and %s or %d. So something like this

with open('drivers.csv', 'r') as file:
  for row in file:
    db.execute("INSERT INTO drivers(driverId, driverRef, number, code, forename, surname, dob, nationality, url) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % row.split(","))
    #instead of directly inserting into the database you should probably first test it, like so:
    #print("INSERT INTO drivers(driverId, driverRef, number, code, forename, surname, dob, nationality, url) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % row.split(","))

However, that will probably still give errors for decimals values or empty NaN values. And also, it doesn't check if the csv contains too many or too few columns.

Python/Sql - Creating a table and loading csv file

1 Answers