使用python搜索mysql表以根据数据输入打印结果

我有一个使用两个表作为参数的MySQL数据库。

tbl_manufacturer (manufacturer_id, manufacturer)
tbl_model (model_id, manufacturer_id, model)

I've got this script at the moment which runs through my test_data and prints the corresponding manufacturer info found (which in this case is the manufacturer_id)

import re

manufacturer_info = [('1', 'browning', ['Browning']),
                 ('2', 'fabarm', ['Fab Arm', 'Fabarm', 'Fab arms']),
                 ('3', 'nikko', ['Nikko', 'Niko', 'Nicco', 'nico']),
                 ('4', 'ugartechea', ['Ugartechea', 'Ugartecea', 'Uartechea']),
                 ('5', 'Miroku', ['Miroku', 'Miroko', 'Miroka']),
                 ('6', 'blaser', ['Blaser', 'Blazer']),
                 ('7', 'beretta', ['Beretta', 'Bereta', 'Berretta', 'Berreta']),
                 ('8', 'webandscott', ['Webley & Scott', 'Webley and Scott']),
                 ('9', 'perazzi', ['Perazzi', 'Perazi', 'Perrazzi', 'Perrazi']),
                 ('10', 'krieghoff', ['Krieghoff', 'Krieghof', 'Kreighoff']),
                 ('11', 'laurona', ['Laurona', 'Lauraona', 'Laurina']),
                 ('12', 'cz', ['CZ', 'Czech Armory', 'Ceska Zbrojovka']),
                 ('13', 'anschutz', ['Anschutz', 'Anshutz', 'Ancshutz', 'Anchutz'])]

manufacturer_group_dict = {patt_name: full_name for full_name, patt_name, _ in manufacturer_info}
group_patts = []
for _, patt_name, syns in manufacturer_info:
    group_patts.append(f"(?P<{patt_name}>{'|'.join([re.escape(curr_syn) for curr_syn in syns])})")
all_groups_patt = '|'.join(group_patts)
manufacturer_patt = re.compile(rf"(?:(?<=^)|(?<=\s))(?:{all_groups_patt})(?=[\s.,:]|$)", re.MULTILINE | re.IGNORECASE)

def extract_manufacturer(str_in):
    res_list = []
    for curr_res in manufacturer_patt.finditer(str_in):
        curr_dict = curr_res.groupdict()
        for k, v in curr_dict.items():
            if v:
                res_list.append((manufacturer_group_dict[k], v))
                break
    return res_list

test_data = ["Browning 12 gauge B725 Pro Sport Adjustable",
"Fab arms 12 gauge Shotgun",
"Nikko 12 GAUGE KFC",
"Browning 12 gauge Cynergy",
"Ugartechea 12 G 28 G Shotgun",
"Browning 12 gauge B725 Sporter",
"Miroku .22-250 gauge MK 60 Grade 5",
"Blaser 12 gauge F3 Professional (GRADE 6)",
"Beretta 410 gauge",
"Webley & Scott 410 Gauge Shotgun",
"Perazzi 410G MX2000S (ADJUSTABLE STOCK)",
"Blaser 410 G F3 CUSTOM",
"Krieghoff 12G K80 Parcours",
"Miroku 12 Gauge MK 38 Grade 5",
"Miroku 12 gauge MK 38 Grade 5 Sporter",
"Browning 12 gauge B525 EXCUISITE",
"Ceska Zbrojovka .22LR 452 Varmint (THUMBHOLE LAMINATED)",
"Laurona 12 gauge Shotgun",
"Miroku 7.62x52 gauge MK 60 Grade 5",
"Blaser 10 gauge (BARRELS ONLY)",
"Anschutz .22 LR 1415"]

manufacturer_matches_info = [extract_manufacturer(elem) for elem in test_data]

#print(manufacturer_matches_info)

manufacturer_matches_group = [[match_group for match_group, _ in curr_str_matches] for curr_str_matches in manufacturer_matches_info]

makeitastring = ''.join(map(str, manufacturer_matches_group))
string = makeitastring.replace("[","")
strg = string.replace("]","")
manufacturer = strg.replace("'","")
print(manufacturer)

It then occured to me that surely there is a better way to do this, only by using the data within the tables, rather than having to retype everything for my manufacturer_info / model_info (I have 900 manufacturers and around 6000 models in the data).

理想情况下,我希望该脚本能够运行test_data并根据test_data条目打印所有Manufacturer_id和model_id。

目前,我正在寻找拼写错误,但是可以将其删除,因为它并不是真正需要的。

因此,最终结果是:

print(manufacturer)
print(model)

例如,输出test_data(Browning 12仪表B725 Pro Sport Adjustable)数据的第一个示例将产生输出:

1
5*

*If B725 Pro Sport Adjustable was the 5th model_id in my tbl_model table.