def set_type(type, connection):
"""Return the type id"""
result = sqlite.read(connection, f"SELECT id from TYPES where NAME = '{type}' ")
if len(result) == 0:
sqlite.update(connection, f"INSERT INTO TYPES (NAME) VALUES ('{type}')")
return sqlite.read(connection, f"SELECT id from TYPES where NAME = '{type}' ")[0][0]
else:
return result[0][0]
def geolocate(db, table, rows):
nrows = len(sqlite.getRows(db, table, fields=None))
print nrows
for r in range(1,nrows+1):
row = sqlite.getRow(db, table, id=r)
if row['lat'] == None:
g = geocoder.arcgis(row['address'])
pos = g.latlng
if pos != None:
sqlite.update(db, table, {'lat':pos[0], 'lng': pos[1]}, {'ROWID':r})
else:
print row['address']
return
def find_hazardous_materials(info, connection):
"""Return ids of the associated hazardous materials"""
associated_hazardous_materials = info.find_elements_by_xpath('.//div[contains(@class ,"hazardous-material")]')
hazardous_materials_ids = []
for material_info in associated_hazardous_materials:
material = material_info.get_attribute("title")
# Check if material is in database, otherwise store it
result = sqlite.read(connection, f"SELECT id from ALL_HAZARDUOS_MATERIALS where NAME = '{material}' ")
if len(result) == 0:
sqlite.update(connection, f"INSERT INTO ALL_HAZARDUOS_MATERIALS (NAME) VALUES ('{material}')")
id = sqlite.read(connection, f"SELECT id from ALL_HAZARDUOS_MATERIALS where NAME = '{material}' ")[0][0]
else:
id = result[0][0]
hazardous_materials_ids.append(id)
return hazardous_materials_ids
def i_update():
"""
Sélectionne un contact pour mise à jour
"""
part_name = input("Tapez le début du nom à modifier: ").lower()
lst = db.search(data, part_name)
if len(lst) != 0:
for old_contact in lst:
i_print_contact(old_contact)
if question("Voulez-vous modifier ce contact ?"):
new_l_name = input(
"Entrez le nouveau nom du contact ou laissez vide pour conserver: "
) or old_contact.l_name
new_f_name = input(
"Entrez le nouveau prénom du contact ou laissez vide pour conserver: "
) or old_contact.f_name
new_tel = input(
"Entrez le nouveau n° de téléphone du contact ou laissez vide pour conserver: "
) or old_contact.tel
while not verif(new_tel):
new_tel = input(
"N° incorrect. Vérifiez et retapez le code: ")
new_contact = Contact(new_f_name, new_l_name, new_tel)
db.update(data, old_contact, new_contact)
city = r['pub'].split(':')[0]
city = city.strip(' []')
city = city.split('[')[0].strip(' ').strip('?').replace("'",'')
result['city']=city
ed = r['pub'].split(':')[1].split(',')[0].strip(' ').replace("'",'')
result['editor']=ed
year = re.findall(r'\d\d\d\d', r['pub'])
year = year[0] if len(year)>0 else None
result['editor_year']=year
results.append(result)
sqlite.update(db, table, results, {'ROWID': r['rowid']})
results =[]
# print r['pub']
# print city
# print ed
# print year
# print
#print '.'
print 'done!'
'''
after sql
update booksfull set editor = replace(editor, '[', '')
def create_database(connection):
"""Initialize the database, creating the tables"""
sqlite.update(connection, '''CREATE TABLE RECYCLEABLES
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
TYPE INT NOT NULL,
EXTRA TEXT);''')
sqlite.update(connection, '''CREATE TABLE SYNONYMS
(ID INT NOT NULL,
NAME TEXT NOT NULL);''')
sqlite.update(connection, '''CREATE TABLE TYPES
(ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT NOT NULL);''')
sqlite.update(connection, '''CREATE TABLE RECYCLE_PLACES
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL);''')
sqlite.update(connection, '''CREATE TABLE ALL_HAZARDUOS_MATERIALS
(ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT NOT NULL);''')
sqlite.update(connection, '''CREATE TABLE ASSOCIATED_HAZARDUOS_MATERIALS
(ITEM INT NOT NULL,
MATERIAL INT NOT NULL);''')
def store_associated_hazarduos_materials(id, hazardous_materials, connection):
for material in hazardous_materials:
sqlite.update(connection, f"INSERT INTO ASSOCIATED_HAZARDUOS_MATERIALS (ITEM,MATERIAL) VALUES ({id}, {material})")
def store_synonyms(synonyms, id, connection):
for synonym in synonyms:
sqlite.update(connection, f"INSERT INTO SYNONYMS (ID,NAME) VALUES ({id}, '{synonym}')")
def store_recyclable(id, name, type_id, connection):
sqlite.update(connection, f"INSERT INTO RECYCLEABLES (ID,NAME,TYPE) VALUES ({id}, '{name}', {type_id})")