Hey all, I’m working with virtual doctype and i want to insert, fetch and list the documents from a remote database (MySQL). I got the connection and listed the documents in the list view.
the doctype has a childtable with 2 fields (Subject and Mark).
when i try to insert a new record, im getting a dluplicationError.
the code for saving the document is
def db_insert(self, *args, **kwargs):
main_data = self.get_valid_dict(convert_dates_to_str=True)
child_table_data = self.get("details")
main_columns = ", ".join(main_data.keys())
main_values = ", ".join(["%s" for _ in main_data.values()])
main_query = f"INSERT INTO `tabSample` ({main_columns}) VALUES ({main_values})"
cn = conn()
cur = cn.cursor()
cur.execute(main_query, tuple(main_data.values()))
cur.execute("SELECT LAST_INSERT_ID()")
new_id = str(self.name)
print(f"\n\n\n\n\n{new_id}\n\n\n\n")
for child_row in child_table_data:
child_row.parent = new_id
child_row.insert()
# child_row["parent"] = new_id
# child_columns = ", ".join(child_row.keys())
# child_values = ", ".join([ "%s" for _ in child_row.values() ])
# child_query = f"INSERT INTO `tabsub table` ({child_columns}) values ({child_values})"
# cur.execute(child_query, tuple(child_row.values()))
cn.commit()
and the code to view the code is
def load_from_db(self):
doc = str(self.name)
cn = conn()
cur = cn.cursor()
query = f"SELECT * FROM `tabSample` WHERE name = ?"
cur.execute(query, (doc,))
head = [ x[0] for x in cur.description ]
results = cur.fetchall()
d = []
for i in results:
d.append(dict(zip(head, i)))
if d:
main_data = d[0]
for field, value in main_data.items():
if field in self.fields:
self.set(field, value)
child_table_data = self.get("details")
self.set("details", [])
for row in child_table_data:
child_row = self.append("details")
child_row.subject = row.subject
child_row.mark = row.mark
field = "_table_fieldnames"
values = True
d[0][field] = values
target_item = next( (item for item in d), None)
super(Document, self).__init__(target_item)
and to update,
def db_update(self, *args, **kwargs):
doc = str(self.name)
main_data = self.get_valid_dict(convert_dates_to_str=True)
child_table_data = self.get("details")
main_columns = ", ".join([f"{key} = %s" for key in main_data.keys()])
# main_values = ", ".join(["%s" for _ in main_data.values()])
query = f"UPDATE `tabSample` SET {main_columns} WHERE name =\"{doc}\""
cn = conn()
cur = cn.cursor()
cur.execute(query, tuple(main_data.values()))
for child_row in child_table_data:
child_row["parent"] = doc
child_columns = ", ".join([f"{key} = %s" for key in child_row.keys()])
child_query = f"UPDATE `tabsub table` SET {child_columns} WHERE parent = \"{doc}\""
cur.execute(child_query, (child_row["subject"], child_row["mark"]))
# fields = [ field.strip() for field in columns.split(',') ]
# formatted_fields = [ f"{field} = %s" for field in fields ]
# columns = ", ".join(formatted_fields)
# cur.execute(query, tuple(main_data.values()))
cn.commit()
Can anyone help me to solve this?