CODEWITHSUNDEEP
python-2.7tkintersqlite
maxpepsi
maxpepsi

Reputation: 33

How to use sqlite to get values from 2 tables

I have made 2 tables using sqlite in python and i think ive done it right so they are linked with a foreign key. If my code is correct does anybody know how to get the values. The code from making the tables is below as well as the code for getting the values which returns nothing.

c.execute("SELECT value AND wage FROM classes, yourplayers WHERE Firstname=? AND Secondname=?", (self.Seller_Firstname, self.Seller_Lastname))


c.execute("CREATE TABLE IF NOT EXISTS classes(class INT NOT NULL, wage INT NOT NULL, value INT NOT NULL)")
c.execute("INSERT INTO classes(class, wage, value) VALUES (1, 1000, 5000000)")
c.execute("INSERT INTO classes(class, wage, value) VALUES (2, 5000, 1000000)")
c.execute("INSERT INTO classes(class, wage, value) VALUES (3, 10000, 15000000)")
c.execute("INSERT INTO classes(class, wage, value) VALUES (4, 25000, 20000000)")
c.execute("INSERT INTO classes(class, wage, value) VALUES (5, 50000, 25000000)")
c.execute("INSERT INTO classes(class, wage, value) VALUES (6, 100000, 35000000)")
c.execute("INSERT INTO classes(class, wage, value) VALUES (7, 150000, 50000000)")
c.execute("INSERT INTO classes(class, wage, value) VALUES (8, 225000, 80000000)")
c.execute("SELECT * FROM classes")
conn.commit()
print(c.fetchall())

c.execute('DROP TABLE IF EXISTS yourplayers')
c.execute('CREATE TABLE yourplayers(PlayerId INT PRIMARY KEY NOT NULL, Firstname VARCHAR(10) NOT NULL, Secondname VARCHAR(10) NOT NULL, contract INT NOT NULL, rating INT NOT NULL, class INT NOT NULL, morale INT NOT NULL, FOREIGN KEY (class) REFERENCES classes(class))')
x = 1
for x in range(20):
    FN = open("firstnames", "r")
    FNames = FN.read()
    listF = FNames.split('\n')
    y = randint(0,55)
    N1 = listF[y]
    N1 = str(N1)
    SN = open("lastnames", "r")
    SNames = SN.read()
    listS = SNames.split('\n')
    z = randint(0, 9)
    N2 = listS[z]
    N2 = str(N2)
    c.execute("INSERT INTO yourplayers(PlayerId, Firstname, Secondname, contract, rating, class, morale) VALUES (? , ?, ?, 2, 55, 1, 55)",
              (x, N1, N2))
    x = x+1
c.execute('SELECT * FROM yourplayers')
conn.commit()

Each paragraph is in a seperate function but ive removed irrelevant code

Upvotes: 0

Views: 58

Answers (1)

CL.
CL.

Reputation: 180070

A foreign key constraint is just a constraint, i.e., it prevents you from inserting data that would violate the constraint.

Foreign key constraints have no effect on queries; if you want to join tables through these values, you still have to write the join:

SELECT value, wage     -- no AND here
FROM classes
JOIN yourplayers USING (class)
WHERE Firstname=?
  AND Secondname=?;

Upvotes: 1

Related Questions