1

I have to do some work with a SQL Database. I have done a bit of research on with video but I got very confused Could you please explain to me how I insert python lists index into a SQL database?

I'm trying to make the Location a foreign key from its own table. This will allow for easier access in the future so that I can call all the cities from one country with a WHERE statement. I'm doing this so that I can scale this in the future to something else but I want to know how to do it with this example.

LocationID - United Kingdom = 1 France = 2 Denmark = 3 Germany = 4

Place: London + LocationID1

This is how I want it to look.

This is what I have so far:

import sqlite3
Location = ["United Kingdom","Germany","France","Denmark"]
Places = ["London","Tottenham","Munich","Paris","Frankfurt"]

conn = sqlite3.connect("Location.db")
c = conn.cursor()
c.execute("INSERT INTO Places Location[0])
conn.commit()
conn.close()

This returns an error for me I don't know if I need to provide the database file. Just ask me if you need it to assist me

ShadowPoke3
  • 21
  • 5
  • The syntax of `INSERT` is `INSERT INTO tablename (col1, col2, ...) VALUES (col1Value, col2Value, ...)` – Barmar Sep 20 '21 at 20:01
  • Use query parameters. See https://www.kite.com/python/answers/how-to-bind-variables-to-a-sql-query-in-sqlite3-in-python – Barmar Sep 20 '21 at 20:02
  • I'll try that now. Do you have any idea, how I can link the IDs from Location into the Places table – ShadowPoke3 Sep 20 '21 at 20:14
  • See https://stackoverflow.com/questions/2548493/how-do-i-get-the-id-after-insert-into-mysql-database-with-python – Barmar Sep 20 '21 at 20:20
  • Thank you, I'll look over the link now – ShadowPoke3 Sep 20 '21 at 20:23
  • @Barmar Could you explain how .lastrowid works? – ShadowPoke3 Sep 20 '21 at 20:34
  • After you insert into a table that uses `AUTO_INCREMENT`, `cursor.lastrowid` will contain the ID that was assigned to that row. So after you insert `United Kingdom` into the `Location` table, `cursor.lastrowid` will contain its ID. Save that in a variable and use it when inserting all the UK cities into the `Places` table. – Barmar Sep 20 '21 at 20:36
  • Thanks for explaining it. I'm starting to understand this now – ShadowPoke3 Sep 20 '21 at 20:39
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Sep 27 '21 at 14:51

0 Answers0