Insert foreign keys in multiple MySQL tables using Python function

user_16408002 0
Viewed 128

I have multiple tables for which I have to insert foreign keys. Starting from dictionary (i.e. part of the one I'm using, d_fk = {'Bien_Immo': ['fk_Id_Lot', 'Lot', 'Id']}). It gives entries to 'create_fk()' function that will insert F_keys.

'Bien_Immo': table in which fk will be inserted 'fk_Id_Lot': name of fk 'Lot': name of parent table 'Id': name of parent column

Here is the code that return an error SQL syntax (1064)

def create_fk(name1, name2, name3, name4):
    url = "mysql+pymysql://{user}:{password}@localhost/{db}"
    engine = create_engine(url.format(user='root', password='******', db='data_immo'))
    engine.execute(f"ALTER TABLE {name1}"
                   f"ADD COLUMN {name2},"
                   f"ADD CONSTRAINT {name2}"
                   f"ADD FOREIGN KEY ({name2}) REFERENCES {name3}({name4})"
                   f"ON DELETE CASCADE")

for key in d_fk.keys():
    if isinstance(d_fk[key][0], list):
        for i in range(len(d_fk[key])):
            [n2, n3, n4] = [d_fk[key][i][0], d_fk[key][i][1], d_fk[key][i][2]]
            create_fk(key, n2, n3, n4)
    else:
        [n2, n3, n4] = [d_fk[key][0], d_fk[key][1], d_fk[key][2]]
        create_fk(key, n2, n3, n4)

Tank you for your help on this

mysql python
2 Answers

The problem in the SQL is you want to add a column but you don't specify a datatype for it.

In order for your SQL to work you must specify a datatype that matches the name4 column datatype.

For instance, if name4 is bigint this will work:

engine.execute(f"ALTER TABLE {name1}"
                   f"ADD COLUMN {name2} bigint,"
                   f"ADD CONSTRAINT {name2}"
                   f"ADD FOREIGN KEY ({name2}) REFERENCES {name3}({name4})"
                   f"ON DELETE CASCADE")
user_67603720

this works fine

engine.execute(f"ALTER TABLE {name1} ADD COLUMN {name2} int, ADD FOREIGN KEY ({name2}) REFERENCES {name3}({name4}) ON DELETE CASCADE")
user_164080020
Related