Python list into SQL column

Question

I have two or more lists, for example:

    name = ['Chris', 'John', 'Louis']
    surname = ['Brown', 'Green', 'Red']
    car = ['Audi', 'Mercedes', 'Ferrari']

What I'd like to achieve is creating an sql database where every list is a column so that it would be something like: "Chris" in the first line, "Brown" in the second line and "Audi" in the third one - and so on. My actual python script is the following one but I can't figure out how I can save the list:

    connection = sqlite3.connect("name.db")                       
    cursor = connection.cursor()                                  
    cursor.execute("DROP TABLE IF EXISTS peopleList")           
    cursor.execute("CREATE TABLE peopleList(Name TEXT, Surname TEXT, Car TEXT)")

mechanical_meat · Accepted Answer · 2017-08-30 23:05:02Z

7

You can use zip() and executemany() like so:

cursor.executemany("""INSERT INTO peopleList (Name, Surname, Car) VALUES (?,?,?)""", 
                   zip(name,surname,car))

edited Aug 30, 2017 at 23:05

answered Aug 30, 2017 at 22:51

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

antonioag Over a year ago

I got a traceback error: sqlite3.OperationalError: near "%": syntax error

mechanical_meat Over a year ago

@antonioag: oh I see now you're using SQLite3. The question was titled and tagged with MySQL so that confused me. Please see edited answer which should work.

antonioag Over a year ago

Sorry for the wrong tag and thank you for your time. Anyway, no more errors but the file created has 0 rows

mechanical_meat Over a year ago

Make sure you do connection.commit() after the INSERT statement.

Collectives™ on Stack Overflow

Python list into SQL column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related