CODEWITHSUNDEEP
pythondatabasesqliterelational
user3018178
user3018178

Reputation: 11

(Python, sqlite3) Building relational database?

I am trying to create a relational database in python with sqlite3. I am a little fussy on how to connect the tables in the database so that one entity connects to another via the second table. I want to be able to make a search on a persons name via a webpage and then find the parents related to that person. Im not sure if I need two tables or three.

This is how my code looks like right now:

class Database:
   '''Initiates the database.'''
   def __init__(self):
      self.db = sqlite3.connect('family2.db')
   def createTable(self):
      r = self.db.execute('''
      CREATE TABLE IF NOT EXISTS family2 (
      id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
      fname TEXT,
      sname TEXT,
      birthdate TEXT,
      deathdate TEXT,
      mother TEXT,
      father TEXT
      )''')
      self.db.commit()

      g = self.db.execute('''CREATE TABLE IF NOT EXISTS parents(
      id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
      mother TEXT,
      father TEXT)''')  
      self.db.commit()

      b = self.db.execute('''CREATE TABLE IF NOT EXISTS relations(
      id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
      family2,      
      parents TEXT
      )''')
      self.db.commit()

Thanks in advance!

Upvotes: 0

Views: 1480

Answers (1)

CL.
CL.

Reputation: 180020

You don't need multiple tables; you can store the IDs of the parents in the table itself:

CREATE TABLE persons(
    id INTEGER PRIMARY KEY,
    name TEXT,
    mother_id INT,
    father_id INT
);

You can then find the mother of a person that is identified by its name with a query like this:

SELECT *
FROM persons
WHERE id = (SELECT mother_id
            FROM persons
            WHERE name = '...')

Upvotes: 1

Related Questions