Setting up a Relational Table

I want to add a relational data without dealing id's. Let me explain. For example, Nokta1 and Nokta2 is related to Cihaz1. Nokta3 is related to Cihaz2. So this is a one-to-many relation.

Immediately after adding Cihaz1 and Cihaz2 rows to cihaz table, how can I insert Nokta1, Nokta2 and Nokt开发者_运维百科a3 rows to nokta table with these relations?

Is there any error in my code?

import sqlite3

# data
NOKTALAR = (
    ('Nokta1', 'AO', 'CVXY', '1'),
    ('Nokta2', 'AO', 'CVXY', '1'),
    ('Nokta3', 'BO', 'MESR', '1'),
    ('Nokta4', 'BO', 'MESR', '1'),
    ('Nokta5', 'BI', 'APTU', '2'),
    ('Nokta6', 'AI', 'FTRE', '1'),
    ('Nokta7', 'AI', 'FTRE', '1'),
)
CIHAZLAR = (
    ('Cihaz1'),
    ('Cihaz2'),
    ('Cihaz3'),
    ('Cihaz4'),
    ('Cihaz5'),
    ('Cihaz6'),
)

# get connection and cursor objects
conn = sqlite3.connect('iodatabase')
c = conn.cursor()

# create tables
c.execute('''create table cihaz (
    id integer primary key autoincrement,
    name text
)''')

c.execute('''create table nokta (
    id integer primary key autoincrement,
    name text,
    module text,
    type text,
    desc text,
    cihaz_id integer
    FOREIGN KEY(cihaz_id) REFERENCES cihaz(id)
)''')
c.execute('''create table link (
    nokta_id integer,
    cihaz_id integer,
)''')

If you want to relate them by name and not by ids, then just use a foreign key to the name.

c.execute('''create table nokta
             id integer primary key autoincrement,
             name text,
             parent text
             FOREIGNKEY(parent) REFERENCES cihaz(name)''')

Now when you insert a nokta, just use the appropriate parent.

 c.execute('insert into nokta (text, parent) values ?, ?', ('Nokta1', 'Cihaz1'))

Now to query for all nokta related to a particular cihaz, just use the name.

 c.execute('select * from nokta where parent = ?', ('Cihaz1',))

The link table should be defined as:

   create table link (     
       nokta_id integer,      
       cihaz_id integer,
       FOREIGN KEY(nokta_id) REFERENCES nokta(id),
       FOREIGN KEY(cihaz_id) REFERENCES cihaz(id)
   )   

but you really don't need this table at all. It is just a projection of the Nokta table over the attributes id and cihaz_id (i.e. equivalent to select id, chaz_id from Nokta).

Immediately after adding "Cihaz1" and "Cihaz2" rows to "cihaz" table, how can I >insert "Nokta1", "Nokta2" and "Nokta3" rows to "nokta" table with these relations?

There should be no problem adding Nokta1, Nokta2, and Nokta3 as long as they only reference existing values in the cihaz table.

e.g.

INSERT INTO CIHAZ(name) VALUES('Cihaz1');
...
INSERT INTO CIHAZ(name) VALUES('Cihaz7');

INSERT INTO NOKTA(name, module, type, desc, cihaz_id) VALUES ('Nokta1', 'AO', 'CVXY', '1', 1),

For the last field (the foreign key field) values 1, 2, 3, 4, 5, 6, or 7 would be ok, because we have already inserted rows with those keys into CIHAZ (automatically created).