CODEWITHSUNDEEP
pythonsqlite
zds
zds

Reputation: 964

How to create unique rows in a table?

I`m just started to learn SQLite. I use python. The question is how to create rows in tables, so that they are uniqe by name and how to use (extract) id1 and id2 to insert them into a separate table.

import sqlite3

conn = sqlite3.connect('my.db')
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS table1(
          id1 integer primary key autoincrement, name)''')
c.execute('''CREATE TABLE IF NOT EXISTS table2(
          id2 integer primary key autoincrement, name)''')
c.execute('CREATE TABLE IF NOT EXISTS t1_t2(id1, id2)') # many-to-many
conn.commit()
conn.close()


conn = sqlite3.connect('my.db')
c = conn.cursor()
c.execute('INSERT INTO table1 VALUES (null, "Sue Monk Kidd")')
c.execute('INSERT INTO table2 VALUES (null, "The Invention of Wings")')
#c.execute('INSERT INTO t1_t2 VALUES (id1, id2)')
c.execute('INSERT INTO table1 VALUES (null, "Colleen Hoover")')
c.execute('INSERT INTO table2 VALUES (null, "Maybe Someday")')
#c.execute('INSERT INTO t1_t2 VALUES (id1, id2)')

Thanks.

Upvotes: 0

Views: 250

Answers (2)

zds
zds

Reputation: 964

I found the problem of unique names on unique column problem.

Actually, I should change INSERT to INSERT OR IGNORE

import sqlite3

conn = sqlite3.connect('my.db')
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS table1(
          id1 integer primary key autoincrement, name TEXT unique)''')
c.execute('''CREATE TABLE IF NOT EXISTS table2(
          id2 integer primary key autoincrement, name TEXT unique)''')
c.execute('CREATE TABLE IF NOT EXISTS t1_t2(id1, id2)') # many-to-many
conn.commit()
conn.close()


conn = sqlite3.connect('my.db')
c = conn.cursor()
c.execute('INSERT OR IGNORE INTO table1 VALUES (null, "Sue Monk Kidd")')
c.execute('INSERT OR IGNORE INTO table2 VALUES (null, "The Invention of Wings")')

Upvotes: 0

duffymo
duffymo

Reputation: 308868

I think you have some problems with the table creation. I doubt that it worked, because the name columns don't have a type. They should probably be varchar of some length. The JOIN table definition isn't right, either.

CREATE TABLE IF NOT EXISTS table1 (
    id1 integer primary key autoincrement, 
    name varchar(80)
);

CREATE TABLE IF NOT EXISTS table2 (
    id2 integer primary key autoincrement, 
    name varchar(80)
);


CREATE TABLE IF NOT EXISTS t1_t2 (
    id1 integer,
    id2 integer,
    primary key(id1, id2),
    foreign key(id1) references table1(id1),
    foreign key(id2) references table2(id2) 
);

I would not create the tables in code. Script them, execute in the SQLite admin, and have the tables ready to go when your Python app runs.

I would think much harder about your table names if these are more than examples.

Upvotes: 1

Related Questions