Spaces:
Runtime error
Runtime error
| #!/usr/bin/env python3 | |
| """ | |
| Master Tree Database for TreeTrack | |
| Pre-loaded species data for auto-suggestions and completions | |
| """ | |
| import sqlite3 | |
| import json | |
| from pathlib import Path | |
| from datetime import datetime | |
| import logging | |
| logger = logging.getLogger(__name__) | |
| # Master tree species data from Tezpur research team | |
| MASTER_TREE_DATA = [ | |
| {"id": 1, "local_name": "", "scientific_name": "Abroma augusta", "fruiting_season": "", "tree_code": "AA"}, | |
| {"id": 2, "local_name": "", "scientific_name": "Abrus precatorius", "fruiting_season": "", "tree_code": "AP"}, | |
| {"id": 3, "local_name": "Khair", "scientific_name": "Acacia catechu", "fruiting_season": "", "tree_code": "AC"}, | |
| {"id": 4, "local_name": "Jungli chandan", "scientific_name": "Adenanthera pavonina", "fruiting_season": "March to august", "tree_code": "AP2"}, | |
| {"id": 5, "local_name": "", "scientific_name": "Aeschynomene americanum", "fruiting_season": "", "tree_code": "AA2"}, | |
| {"id": 6, "local_name": "", "scientific_name": "Ageratum conyzoides", "fruiting_season": "", "tree_code": "AC2"}, | |
| {"id": 7, "local_name": "Amari", "scientific_name": "Aglaia spectabilis", "fruiting_season": "May to august", "tree_code": "AS"}, | |
| {"id": 8, "local_name": "Borpat", "scientific_name": "Ailanthus integrifolia", "fruiting_season": "October to april", "tree_code": "AI"}, | |
| {"id": 9, "local_name": "Sika moroliya", "scientific_name": "Alangium chinense", "fruiting_season": "July to november", "tree_code": "AC3"}, | |
| {"id": 10, "local_name": "Siris", "scientific_name": "Albizia", "fruiting_season": "", "tree_code": "AA3"}, | |
| {"id": 11, "local_name": "Koroi", "scientific_name": "Albizia procera", "fruiting_season": "December to may", "tree_code": "AP3"}, | |
| {"id": 12, "local_name": "Boga siris", "scientific_name": "Albizia procera", "fruiting_season": "", "tree_code": "AP4"}, | |
| {"id": 13, "local_name": "", "scientific_name": "Albizzia odoratissima", "fruiting_season": "", "tree_code": "AO"}, | |
| {"id": 14, "local_name": "Chatiana", "scientific_name": "Alstonia scholaris", "fruiting_season": "January to February", "tree_code": "AS2"}, | |
| {"id": 15, "local_name": "Kali gos", "scientific_name": "Altingia excelsa", "fruiting_season": "", "tree_code": "AE"}, | |
| {"id": 16, "local_name": "Agar", "scientific_name": "Aquilaria malaccensis", "fruiting_season": "April to september", "tree_code": "AM"}, | |
| {"id": 17, "local_name": "Bachahu", "scientific_name": "Archidendron bigeminum", "fruiting_season": "April to May", "tree_code": "AB"}, | |
| {"id": 18, "local_name": "Shamkathal", "scientific_name": "Artocarpus chaplasha", "fruiting_season": "March to July", "tree_code": "AC4"}, | |
| {"id": 19, "local_name": "Neem", "scientific_name": "Azadirachta indica", "fruiting_season": "", "tree_code": "AI2"}, | |
| {"id": 20, "local_name": "Leteku", "scientific_name": "Baccaurea ramiflora", "fruiting_season": "May to August", "tree_code": "BR"}, | |
| {"id": 21, "local_name": "Kanchan", "scientific_name": "Bauhinia purpurea", "fruiting_season": "January to March", "tree_code": "BP"}, | |
| {"id": 22, "local_name": "No local name in use", "scientific_name": "Beilschmiedia assamica", "fruiting_season": "", "tree_code": "BA"}, | |
| {"id": 23, "local_name": "", "scientific_name": "Bidens sp.", "fruiting_season": "", "tree_code": "BS"}, | |
| {"id": 24, "local_name": "Mebu/Uriam", "scientific_name": "Bischofia javanica", "fruiting_season": "September to January", "tree_code": "BJ"}, | |
| {"id": 25, "local_name": "Semal", "scientific_name": "Bombax ceiba", "fruiting_season": "", "tree_code": "BC"}, | |
| {"id": 26, "local_name": "Kuhir", "scientific_name": "Bridelia retusa", "fruiting_season": "October to January", "tree_code": "BR2"}, | |
| {"id": 27, "local_name": "", "scientific_name": "Bridelia tomentosa", "fruiting_season": "", "tree_code": "BT"}, | |
| {"id": 28, "local_name": "Paper Mulberry", "scientific_name": "Broussonetia papyrifera", "fruiting_season": "", "tree_code": "BP2"}, | |
| {"id": 29, "local_name": "", "scientific_name": "Caesalpinia mimosoides", "fruiting_season": "", "tree_code": "CM"}, | |
| {"id": 30, "local_name": "Dhuna", "scientific_name": "Canarium strictum", "fruiting_season": "October to February", "tree_code": "CS"}, | |
| {"id": 31, "local_name": "Sonaru", "scientific_name": "Cassia fistula", "fruiting_season": "", "tree_code": "CF"}, | |
| {"id": 32, "local_name": "Hingori", "scientific_name": "Castanopsis indica", "fruiting_season": "August to November", "tree_code": "CI"}, | |
| {"id": 33, "local_name": "", "scientific_name": "Cayratia sp.", "fruiting_season": "", "tree_code": "CS2"}, | |
| {"id": 34, "local_name": "Banderdima", "scientific_name": "Chisocheton cumingianus/Dysoxylum gotadhora", "fruiting_season": "March to July", "tree_code": "CC"}, | |
| {"id": 35, "local_name": "Lepchipoma", "scientific_name": "Choerospondias axillaris", "fruiting_season": "July to October", "tree_code": "CA"}, | |
| {"id": 36, "local_name": "Bogipoma", "scientific_name": "Chukrasia tabularis", "fruiting_season": "October to February", "tree_code": "CT"}, | |
| {"id": 37, "local_name": "Gonsorai", "scientific_name": "Cinnamomum glaucescens", "fruiting_season": "", "tree_code": "CG"}, | |
| {"id": 38, "local_name": "Dalchini", "scientific_name": "Cinnamomum zeylanicum", "fruiting_season": "", "tree_code": "CZ"}, | |
| {"id": 39, "local_name": "Apung?", "scientific_name": "Clerodendrum", "fruiting_season": "", "tree_code": "CC2"}, | |
| {"id": 40, "local_name": "", "scientific_name": "Corchorus capsularis", "fruiting_season": "", "tree_code": "CC3"}, | |
| {"id": 41, "local_name": "Barun", "scientific_name": "Crataeva religiosa", "fruiting_season": "November to December", "tree_code": "CR"}, | |
| {"id": 42, "local_name": "Ranlung-damdawi", "scientific_name": "Croton caudatus", "fruiting_season": "May to march", "tree_code": "CC4"}, | |
| {"id": 43, "local_name": "", "scientific_name": "Croton sp.", "fruiting_season": "", "tree_code": "CS3"}, | |
| {"id": 44, "local_name": "Sissoo", "scientific_name": "Dalbergia sissoo", "fruiting_season": "", "tree_code": "DS"}, | |
| {"id": 45, "local_name": "", "scientific_name": "Derris scandens", "fruiting_season": "", "tree_code": "DS2"}, | |
| {"id": 46, "local_name": "", "scientific_name": "Derris sp.", "fruiting_season": "", "tree_code": "DS3"}, | |
| {"id": 47, "local_name": "", "scientific_name": "Desmodium triangulare", "fruiting_season": "", "tree_code": "DT"}, | |
| {"id": 48, "local_name": "", "scientific_name": "Desmodium triflorum", "fruiting_season": "", "tree_code": "DT2"}, | |
| {"id": 49, "local_name": "Outenga", "scientific_name": "Dillenia indica", "fruiting_season": "November to April", "tree_code": "DI"}, | |
| {"id": 50, "local_name": "", "scientific_name": "Diospyros sp.", "fruiting_season": "", "tree_code": "DS4"}, | |
| {"id": 51, "local_name": "Khokun", "scientific_name": "Duabanga grandiflora", "fruiting_season": "April to May", "tree_code": "DG"}, | |
| {"id": 52, "local_name": "Banderdima", "scientific_name": "Dysoxylum gotadhora", "fruiting_season": "", "tree_code": "DG2"}, | |
| {"id": 53, "local_name": "Gandhelipoma", "scientific_name": "Dysoxylum mollissimum", "fruiting_season": "January to March", "tree_code": "DM"}, | |
| {"id": 54, "local_name": "Amselleng", "scientific_name": "Dysoxylum procerum", "fruiting_season": "November to April", "tree_code": "DP"}, | |
| {"id": 55, "local_name": "Rudraksh", "scientific_name": "Elaeocarpus sphaericus", "fruiting_season": "October to November", "tree_code": "ES"}, | |
| {"id": 56, "local_name": "Amloki", "scientific_name": "Embelica officinalis", "fruiting_season": "", "tree_code": "EO"}, | |
| {"id": 57, "local_name": "Phulgamari", "scientific_name": "Endospermum chinense", "fruiting_season": "August to November", "tree_code": "EC"}, | |
| {"id": 58, "local_name": "", "scientific_name": "Eupatorium odoratum", "fruiting_season": "", "tree_code": "EO2"}, | |
| {"id": 59, "local_name": "", "scientific_name": "Evolvulus numularias", "fruiting_season": "", "tree_code": "EN"}, | |
| {"id": 60, "local_name": "Borgos", "scientific_name": "Ficus benghalensis", "fruiting_season": "", "tree_code": "FB"}, | |
| {"id": 61, "local_name": "Dabor", "scientific_name": "Ficus drupacea/ Ficus curtipes", "fruiting_season": "August and january", "tree_code": "FD"}, | |
| {"id": 62, "local_name": "Dimaru", "scientific_name": "Ficus glomerata", "fruiting_season": "", "tree_code": "FG"}, | |
| {"id": 63, "local_name": "", "scientific_name": "Ficus hispida", "fruiting_season": "", "tree_code": "FH"}, | |
| {"id": 64, "local_name": "Chepani-dimoru", "scientific_name": "Ficus Nervosa", "fruiting_season": "March to April", "tree_code": "FN"}, | |
| {"id": 65, "local_name": "Mota-bokol-bih", "scientific_name": "Friesodielsia fornicata", "fruiting_season": "October to December", "tree_code": "FF"}, | |
| {"id": 66, "local_name": "Bor thekera", "scientific_name": "Garcinia pedunculata", "fruiting_season": "", "tree_code": "GP"}, | |
| {"id": 67, "local_name": "Kechkechipoma", "scientific_name": "Garuga floribunda", "fruiting_season": "", "tree_code": "GF"}, | |
| {"id": 68, "local_name": "", "scientific_name": "Gliricidia sepium", "fruiting_season": "", "tree_code": "GS"}, | |
| {"id": 69, "local_name": "", "scientific_name": "Glochidion assamensis", "fruiting_season": "", "tree_code": "GA"}, | |
| {"id": 70, "local_name": "Gamari", "scientific_name": "Gmelina arborea", "fruiting_season": "May to September", "tree_code": "GA2"}, | |
| {"id": 71, "local_name": "", "scientific_name": "Grewia sp.", "fruiting_season": "", "tree_code": "GS2"}, | |
| {"id": 72, "local_name": "Gaikhure/Korondo/Keseru", "scientific_name": "Heteropanax fragrans", "fruiting_season": "January to March", "tree_code": "HF"}, | |
| {"id": 73, "local_name": "", "scientific_name": "Hibiscus subdariffa", "fruiting_season": "", "tree_code": "HS"}, | |
| {"id": 74, "local_name": "", "scientific_name": "Hyptis suaveolens", "fruiting_season": "", "tree_code": "HS2"}, | |
| {"id": 75, "local_name": "Pisola", "scientific_name": "Kydia calycina", "fruiting_season": "December to January", "tree_code": "KC"}, | |
| {"id": 76, "local_name": "Ajar", "scientific_name": "Lagerstroemia speciosa", "fruiting_season": "", "tree_code": "LS"}, | |
| {"id": 77, "local_name": "Jia poma", "scientific_name": "Lannea coromandelica", "fruiting_season": "", "tree_code": "LC"}, | |
| {"id": 78, "local_name": "", "scientific_name": "Lantana camara", "fruiting_season": "", "tree_code": "LC2"}, | |
| {"id": 79, "local_name": "Bon gudhi", "scientific_name": "Lepisanthes senegalensis", "fruiting_season": "February to May", "tree_code": "LS2"}, | |
| {"id": 80, "local_name": "Jutuli", "scientific_name": "Liquidambar excelsa", "fruiting_season": "August to November", "tree_code": "LE"}, | |
| {"id": 81, "local_name": "Kaunlo", "scientific_name": "Litsea glutinosa", "fruiting_season": "September to October", "tree_code": "LG"}, | |
| {"id": 82, "local_name": "Baghnala", "scientific_name": "Litsea glutinosa", "fruiting_season": "", "tree_code": "LG2"}, | |
| {"id": 83, "local_name": "Muga", "scientific_name": "Litsea monopetala", "fruiting_season": "June to August", "tree_code": "LM"}, | |
| {"id": 84, "local_name": "", "scientific_name": "Litsea sp.", "fruiting_season": "", "tree_code": "LS3"}, | |
| {"id": 85, "local_name": "Tokko", "scientific_name": "Livistona jenkinsiana", "fruiting_season": "September to February", "tree_code": "LJ"}, | |
| {"id": 86, "local_name": "", "scientific_name": "Macaranga peltata", "fruiting_season": "", "tree_code": "MP"}, | |
| {"id": 87, "local_name": "", "scientific_name": "Macclura cochinchinensis", "fruiting_season": "", "tree_code": "MC"}, | |
| {"id": 88, "local_name": "", "scientific_name": "Maesa indica", "fruiting_season": "", "tree_code": "MI"}, | |
| {"id": 89, "local_name": "Titachampa", "scientific_name": "Magnolia champaca", "fruiting_season": "Throughout the year", "tree_code": "MC2"}, | |
| {"id": 90, "local_name": "Boromthuri", "scientific_name": "Magnolia hodgsonii", "fruiting_season": "May to November", "tree_code": "MH"}, | |
| {"id": 91, "local_name": "Borumthuri", "scientific_name": "Magnolia hodgsonii", "fruiting_season": "May to November", "tree_code": "MH2"}, | |
| {"id": 92, "local_name": "Rohini phal", "scientific_name": "Mallotus philippensis", "fruiting_season": "November to May( Ripening )", "tree_code": "MP2"}, | |
| {"id": 93, "local_name": "Ghora neem", "scientific_name": "Melia azederach", "fruiting_season": "", "tree_code": "MA"}, | |
| {"id": 94, "local_name": "", "scientific_name": "Melia dubia", "fruiting_season": "", "tree_code": "MD"}, | |
| {"id": 95, "local_name": "Sia nahar", "scientific_name": "Mesua assamica", "fruiting_season": "", "tree_code": "MA2"}, | |
| {"id": 96, "local_name": "Nahar", "scientific_name": "Mesua ferrea", "fruiting_season": "March to October", "tree_code": "MF"}, | |
| {"id": 97, "local_name": "Kotkora", "scientific_name": "Meyna spinosa", "fruiting_season": "November to December", "tree_code": "MS"}, | |
| {"id": 98, "local_name": "Sutum tanyi", "scientific_name": "Micromelum", "fruiting_season": "July to September", "tree_code": "MM"}, | |
| {"id": 99, "local_name": "Kari", "scientific_name": "Monoon simiarum", "fruiting_season": "Main fruiting peak- May to July; Minor peak-Dec to Feb", "tree_code": "MS2"}, | |
| {"id": 100, "local_name": "", "scientific_name": "Morinda", "fruiting_season": "", "tree_code": "MM2"}, | |
| {"id": 101, "local_name": "Kamini", "scientific_name": "Murraya paniculata", "fruiting_season": "August to January", "tree_code": "MP3"}, | |
| {"id": 102, "local_name": "", "scientific_name": "Oroxylum indica", "fruiting_season": "", "tree_code": "OI"}, | |
| {"id": 103, "local_name": "Totola", "scientific_name": "Oroxylum indicum", "fruiting_season": "October to December", "tree_code": "OI2"}, | |
| {"id": 104, "local_name": "Manipuri sim", "scientific_name": "Parkia roxburghii", "fruiting_season": "", "tree_code": "PR"}, | |
| {"id": 105, "local_name": "Bonsum", "scientific_name": "Phoebe sp.", "fruiting_season": "July to August", "tree_code": "PS"}, | |
| {"id": 106, "local_name": "Kalakari", "scientific_name": "Picrasma javanica", "fruiting_season": "May to December", "tree_code": "PJ"}, | |
| {"id": 107, "local_name": "", "scientific_name": "Premna benghalensis", "fruiting_season": "", "tree_code": "PB"}, | |
| {"id": 108, "local_name": "Hatipaila", "scientific_name": "Pterospermum acerifolium", "fruiting_season": "May to November", "tree_code": "PA"}, | |
| {"id": 109, "local_name": "Karibadam", "scientific_name": "Pterygota alata", "fruiting_season": "September to January", "tree_code": "PA2"}, | |
| {"id": 110, "local_name": "", "scientific_name": "Rhynchostylis sp.", "fruiting_season": "", "tree_code": "RS"}, | |
| {"id": 111, "local_name": "Agla bel/Biswal", "scientific_name": "Senegalia pennata", "fruiting_season": "October to January", "tree_code": "SP"}, | |
| {"id": 112, "local_name": "", "scientific_name": "Senna hirsuta", "fruiting_season": "", "tree_code": "SH"}, | |
| {"id": 113, "local_name": "", "scientific_name": "Senna tora", "fruiting_season": "", "tree_code": "ST"}, | |
| {"id": 114, "local_name": "Sal", "scientific_name": "Shorea robusta", "fruiting_season": "", "tree_code": "SR"}, | |
| {"id": 115, "local_name": "", "scientific_name": "Sida mysorensis", "fruiting_season": "", "tree_code": "SM"}, | |
| {"id": 116, "local_name": "", "scientific_name": "Sida rhombifolia", "fruiting_season": "", "tree_code": "SR2"}, | |
| {"id": 117, "local_name": "", "scientific_name": "Smilax sp.", "fruiting_season": "", "tree_code": "SS"}, | |
| {"id": 118, "local_name": "Amora tenga", "scientific_name": "Spondias pinnata", "fruiting_season": "November to February", "tree_code": "SP2"}, | |
| {"id": 119, "local_name": "", "scientific_name": "Stephania hernandifolia", "fruiting_season": "", "tree_code": "SH2"}, | |
| {"id": 120, "local_name": "Udal", "scientific_name": "Sterculia villosa", "fruiting_season": "March to May", "tree_code": "SV"}, | |
| {"id": 121, "local_name": "", "scientific_name": "Streblus asper", "fruiting_season": "", "tree_code": "SA"}, | |
| {"id": 122, "local_name": "Jamun", "scientific_name": "Syzygium cumini", "fruiting_season": "May to July", "tree_code": "SC"}, | |
| {"id": 123, "local_name": "Lohajam", "scientific_name": "Syzygium formosum", "fruiting_season": "May to July", "tree_code": "SF"}, | |
| {"id": 124, "local_name": "Panijamun", "scientific_name": "Syzygium syzygioides", "fruiting_season": "", "tree_code": "SS2"}, | |
| {"id": 125, "local_name": "Arjun", "scientific_name": "Terminalia arjuna", "fruiting_season": "", "tree_code": "TA"}, | |
| {"id": 126, "local_name": "Baheda/Behera", "scientific_name": "Terminalia bellirica", "fruiting_season": "", "tree_code": "TB"}, | |
| {"id": 127, "local_name": "Bhomora", "scientific_name": "Terminalia bellirica", "fruiting_season": "", "tree_code": "TB2"}, | |
| {"id": 128, "local_name": "Hilika", "scientific_name": "Terminalia chebula", "fruiting_season": "November to March", "tree_code": "TC"}, | |
| {"id": 129, "local_name": "Hollock", "scientific_name": "Terminalia myriocarpa", "fruiting_season": "October to January", "tree_code": "TM"}, | |
| {"id": 130, "local_name": "Bhelu", "scientific_name": "Tetrameles nudiflora", "fruiting_season": "April to May", "tree_code": "TN"}, | |
| {"id": 131, "local_name": "", "scientific_name": "Tetrastigma sp.", "fruiting_season": "", "tree_code": "TS"}, | |
| {"id": 132, "local_name": "Kauri lota/kukua loti", "scientific_name": "Thunbergia grandiflora", "fruiting_season": "Cold Winter", "tree_code": "TG"}, | |
| {"id": 133, "local_name": "Chikan/Jiban", "scientific_name": "Trema orientalis", "fruiting_season": "July to September", "tree_code": "TO"}, | |
| {"id": 134, "local_name": "", "scientific_name": "Vigna sp.", "fruiting_season": "", "tree_code": "VS"}, | |
| {"id": 135, "local_name": "Panchpatti/Khungsuman/Khong-sman-bol", "scientific_name": "Vitex quinata", "fruiting_season": "Ausgust to September", "tree_code": "VQ"}, | |
| {"id": 136, "local_name": "Panchpatti", "scientific_name": "Vitex sp", "fruiting_season": "", "tree_code": "VS2"}, | |
| {"id": 137, "local_name": "", "scientific_name": "Xylosoma longifolia", "fruiting_season": "", "tree_code": "XL"}, | |
| {"id": 138, "local_name": "Bajrang chota jat", "scientific_name": "Zanthoxylum oxyphyllum", "fruiting_season": "", "tree_code": "ZO"}, | |
| {"id": 139, "local_name": "Bajrang", "scientific_name": "Zanthoxylum rhetsa", "fruiting_season": "March to May", "tree_code": "ZR"}, | |
| {"id": 140, "local_name": "", "scientific_name": "Ziziphus mauritiana (Most probably)", "fruiting_season": "", "tree_code": "ZM"}, | |
| {"id": 141, "local_name": "Unk sapling", "scientific_name": "", "fruiting_season": "", "tree_code": ""}, | |
| {"id": 142, "local_name": "Tengapat", "scientific_name": "", "fruiting_season": "", "tree_code": ""}, | |
| {"id": 143, "local_name": "Maikikori", "scientific_name": "", "fruiting_season": "", "tree_code": ""}, | |
| {"id": 144, "local_name": "Doot gos", "scientific_name": "", "fruiting_season": "", "tree_code": ""}, | |
| {"id": 145, "local_name": "Lali poma", "scientific_name": "", "fruiting_season": "", "tree_code": ""}, | |
| {"id": 146, "local_name": "Tinpatti", "scientific_name": "", "fruiting_season": "", "tree_code": ""}, | |
| ] | |
| def create_master_tree_database(): | |
| """Initialize the master tree species database""" | |
| db_path = Path("data/master_trees.db") | |
| try: | |
| conn = sqlite3.connect(db_path) | |
| cursor = conn.cursor() | |
| # Create master species table | |
| cursor.execute(''' | |
| CREATE TABLE IF NOT EXISTS master_species ( | |
| id INTEGER PRIMARY KEY, | |
| local_name TEXT, | |
| scientific_name TEXT, | |
| fruiting_season TEXT, | |
| tree_code TEXT UNIQUE, | |
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP | |
| ) | |
| ''') | |
| # Create indexes for fast searches | |
| cursor.execute('CREATE INDEX IF NOT EXISTS idx_local_name ON master_species(local_name)') | |
| cursor.execute('CREATE INDEX IF NOT EXISTS idx_scientific_name ON master_species(scientific_name)') | |
| cursor.execute('CREATE INDEX IF NOT EXISTS idx_tree_code ON master_species(tree_code)') | |
| # Insert master data (only if table is empty) | |
| cursor.execute("SELECT COUNT(*) FROM master_species") | |
| if cursor.fetchone()[0] == 0: | |
| insert_query = ''' | |
| INSERT OR IGNORE INTO master_species (local_name, scientific_name, fruiting_season, tree_code) | |
| VALUES (?, ?, ?, ?) | |
| ''' | |
| inserted_count = 0 | |
| for tree in MASTER_TREE_DATA: | |
| # Skip entries with empty tree codes to avoid conflicts | |
| tree_code = tree.get('tree_code', '').strip() | |
| if not tree_code: # Skip empty codes | |
| tree_code = None | |
| cursor.execute(insert_query, ( | |
| tree['local_name'], | |
| tree['scientific_name'], | |
| tree['fruiting_season'], | |
| tree_code | |
| )) | |
| if cursor.rowcount > 0: | |
| inserted_count += 1 | |
| logger.info(f"Master tree database created with {inserted_count} species") | |
| conn.commit() | |
| conn.close() | |
| return True | |
| except Exception as e: | |
| logger.error(f"Failed to create master tree database: {e}") | |
| return False | |
| def get_tree_suggestions(query: str, limit: int = 10): | |
| """Get auto-suggestions for tree names based on query""" | |
| db_path = Path("data/master_trees.db") | |
| if not db_path.exists(): | |
| create_master_tree_database() | |
| try: | |
| conn = sqlite3.connect(db_path) | |
| cursor = conn.cursor() | |
| # Search in both local names and scientific names | |
| search_query = f"%{query.lower()}%" | |
| cursor.execute(''' | |
| SELECT DISTINCT local_name, scientific_name, tree_code, fruiting_season | |
| FROM master_species | |
| WHERE LOWER(local_name) LIKE ? OR LOWER(scientific_name) LIKE ? OR LOWER(tree_code) LIKE ? | |
| ORDER BY | |
| CASE | |
| WHEN LOWER(local_name) = ? THEN 1 | |
| WHEN LOWER(scientific_name) = ? THEN 2 | |
| WHEN LOWER(tree_code) = ? THEN 3 | |
| WHEN LOWER(local_name) LIKE ? THEN 4 | |
| WHEN LOWER(scientific_name) LIKE ? THEN 5 | |
| ELSE 6 | |
| END | |
| LIMIT ? | |
| ''', (search_query, search_query, search_query, | |
| query.lower(), query.lower(), query.lower(), | |
| f"{query.lower()}%", f"{query.lower()}%", limit)) | |
| results = cursor.fetchall() | |
| conn.close() | |
| suggestions = [] | |
| for row in results: | |
| suggestions.append({ | |
| 'local_name': row[0] or '', | |
| 'scientific_name': row[1] or '', | |
| 'tree_code': row[2] or '', | |
| 'fruiting_season': row[3] or '' | |
| }) | |
| return suggestions | |
| except Exception as e: | |
| logger.error(f"Error getting tree suggestions: {e}") | |
| return [] | |
| def get_all_tree_codes(): | |
| """Get all available tree codes for validation""" | |
| db_path = Path("data/master_trees.db") | |
| if not db_path.exists(): | |
| create_master_tree_database() | |
| try: | |
| conn = sqlite3.connect(db_path) | |
| cursor = conn.cursor() | |
| cursor.execute('SELECT DISTINCT tree_code FROM master_species WHERE tree_code != "" ORDER BY tree_code') | |
| results = cursor.fetchall() | |
| conn.close() | |
| return [row[0] for row in results] | |
| except Exception as e: | |
| logger.error(f"Error getting tree codes: {e}") | |
| return [] | |
| if __name__ == "__main__": | |
| create_master_tree_database() | |