Author Topic: SQL query of connectors and usage in Python  (Read 2777 times)

__Andre__

  • EA Novice
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
SQL query of connectors and usage in Python
« on: December 21, 2023, 07:53:01 pm »
Hey everyone
I am an absolute noob with SQL queries.
I want to query the connectors between classes and then use them in Python for further operations.
My problem is that the list that arrives in Python is empty.
If I do the same with an object query, I get the list as it is displayed in the EA also in Python.

Code: [Select]
SELECT
c.Connector_Type AS CLASSTYPE,
c.ea_GUID AS CLASSGUID,
pp_p.Name as Parent_Parent_Package_Name,
c.ea_GUID as Connector_GUID,
c.connector_ID as Connector_ID,
c.connector_Type as Connector_Type,
o2.Name AS Source_Name,
c.Direction AS Direction,
o.Name AS Target_Name
FROM t_connector c
INNER JOIN t_object o ON (c.End_Object_ID = o.Object_ID)
INNER JOIN t_object o2 ON (c.Start_Object_ID = o2.Object_ID)
JOIN t_package p_p ON (o.package_ID = p_p.Package_ID)
JOIN t_package pp_p ON (pp_p.Package_ID = p_p.parent_id)
WHERE pp_p.ea_GUID = '<Search Term>' AND (o.Stereotype = 'Objektdatentyp') AND (c.Connector_Type = 'Realisation')

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 12887
  • Karma: +537/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query of connectors and usage in Python
« Reply #1 on: December 21, 2023, 08:28:21 pm »
How does your list "arrive" in python?

Repository.GetElementSet only returns elements (no connectors)

You'l have to use Repository.SQLQuery to get connectors.

See the method GetConnectorsFromQuery in this script: https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Framework/Utils/Util.vbs

Geert

qwerty

  • EA Guru
  • *****
  • Posts: 13495
  • Karma: +392/-299
  • I'm no guru at all
    • View Profile
Re: SQL query of connectors and usage in Python
« Reply #2 on: December 21, 2023, 10:54:00 pm »
I made a wrapper that parses the XML from EA's sqlquery and returns an array of array. Pretty easy ro handle queries that way.

q.

__Andre__

  • EA Novice
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Re: SQL query of connectors and usage in Python
« Reply #3 on: February 08, 2024, 07:46:34 pm »
Hello Geert

Thanks for the solution. It works perfectly.

Now I have another problem with the SQL query.
I have created a new SQL query in EA (search with SQL builder). When I run the following "code" in EA, I get the expected result.
When I try to run the "code" from Pyhton or with your Excel plugin (eaexcelexporter), I get the following error in EA:
"Error processing XML document: invalid or illegal XML character"

The problem is or starts with the following line:
'o_LO.Name = 'Logical attachment object (LO)'

Do you have a solution for this problem?

Code: [Select]
eaRep = win32com.client.Dispatch("EA.App").Repository # Verbindung zum Enterprise Architect-Modell herstellen
SearchTerm = "{09D06C03-1882-4226-A7E9-6D97DE70F2EC}"

### SQL Abfrage an EA
sqlQueryConnector = """
SELECT 
o.Object_Type AS CLASSTYPE,
o.ea_guid AS GUID,
p_package5.Name AS p_package5,
p_package4.Name AS p_package4,
p_package3.Name AS p_package3,
p_package2.Name AS p_package2,
p_package1.Name AS p_package1,
o.Name,
o.Stereotype,
(
SELECT
COALESCE(GROUP_CONCAT(a_SBB.Name, ''), '')
FROM t_connector c_SBB
LEFT JOIN t_object o_SBB ON c_SBB.End_Object_ID = o_SBB.Object_ID
LEFT Join t_attribute a_SBB ON o_SBB.Object_ID = a_SBB.Object_ID AND o_SBB.Name = 'SBB-CFF-FFS'
WHERE c_SBB.Start_Object_ID = o.Object_ID AND c_SBB.Connector_Type = 'Association' AND o_SBB.Name = 'SBB-CFF-FFS'
) AS 'SBB-CFF-FFS',
(
SELECT
COALESCE(GROUP_CONCAT(a_LO.Name, ''), '')
FROM t_connector c_LO
LEFT JOIN t_object o_LO ON c_LO.End_Object_ID = o_LO.Object_ID
LEFT Join t_attribute a_LO ON o_LO.Object_ID = a_LO.Object_ID AND o_LO.Name = 'Logisches Anlagenobjekt (LO)'
WHERE c_LO.Start_Object_ID = o.Object_ID AND c_LO.Connector_Type = 'Association' AND o_LO.Name = 'Logisches Anlagenobjekt (LO)'
) AS 'Logisches_Anlagenobjekt_(LO)',
(
SELECT
COALESCE(GROUP_CONCAT(a_PO.Name, ''), '')
FROM t_connector c_PO
LEFT JOIN t_object o_PO ON c_PO.End_Object_ID = o_PO.Object_ID
LEFT Join t_attribute a_PO ON o_PO.Object_ID = a_PO.Object_ID AND o_PO.Name = 'Physisches Anlagenobjekt'
WHERE c_PO.Start_Object_ID = o.Object_ID AND c_PO.Connector_Type = 'Association' AND o_PO.Name = 'Physisches Anlagenobjekt'
) AS ['Physisches Anlagenobjekt'],
(
SELECT
COALESCE(GROUP_CONCAT(a_SPEZ.Name, ''), '')
FROM t_connector c_SPEZ
LEFT JOIN t_object o_SPEZ ON c_SPEZ.End_Object_ID = o_SPEZ.Object_ID
LEFT Join t_attribute a_SPEZ ON o_SPEZ.Object_ID = a_SPEZ.Object_ID AND o_SPEZ.Name = 'Spezifikation'
WHERE c_SPEZ.Start_Object_ID = o.Object_ID AND c_SPEZ.Connector_Type = 'Association' AND o_SPEZ.Name = 'Spezifikation'
) AS ['Spezifikation'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_LO.Name, ''), '')
FROM t_connector c_OBJ_LO
LEFT JOIN t_object o_OBJ_LO ON c_OBJ_LO.End_Object_ID = o_OBJ_LO.Object_ID
LEFT Join t_attribute a_OBJ_LO ON o_OBJ_LO.Object_ID = a_OBJ_LO.Object_ID AND o_OBJ_LO.Name LIKE '%LO'
WHERE c_OBJ_LO.Start_Object_ID = o.Object_ID AND c_OBJ_LO.Connector_Type = 'Association' AND o_OBJ_LO.Name LIKE '%LO'
) AS ['OBJ_LO'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_PO.Name, ''), '')
FROM t_connector c_OBJ_PO
LEFT JOIN t_object o_OBJ_PO ON c_OBJ_PO.End_Object_ID = o_OBJ_PO.Object_ID
LEFT Join t_attribute a_OBJ_PO ON o_OBJ_PO.Object_ID = a_OBJ_PO.Object_ID AND o_OBJ_PO.Name LIKE '%PO'
WHERE c_OBJ_PO.Start_Object_ID = o.Object_ID AND c_OBJ_PO.Connector_Type = 'Association' AND o_OBJ_PO.Name LIKE '%PO'
) AS ['OBJ_PO'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_SPEZ.Name, ''), '')
FROM t_connector c_OBJ_SPEZ
LEFT JOIN t_object o_OBJ_SPEZ ON c_OBJ_SPEZ.End_Object_ID = o_OBJ_SPEZ.Object_ID
LEFT Join t_attribute a_OBJ_SPEZ ON o_OBJ_SPEZ.Object_ID = a_OBJ_SPEZ.Object_ID AND o_OBJ_SPEZ.Name LIKE '%SPEZ'
WHERE c_OBJ_SPEZ.Start_Object_ID = o.Object_ID AND c_OBJ_SPEZ.Connector_Type = 'Association' AND o_OBJ_SPEZ.Name LIKE '%SPEZ'
) AS ['OBJ_SPEZ']
FROM t_object o
Inner JOIN t_package p_package1 ON (o.Package_ID = p_package1.Package_ID)
Inner JOIN t_package p_package2 ON (p_package2.package_id = p_package1.Parent_ID)
Inner JOIN t_package p_package3 ON (p_package3.package_id = p_package2.Parent_ID)
Inner Join t_package p_package4 ON (p_package4.package_id = p_package3.Parent_ID)
Inner Join t_package p_package5 ON (p_package5.package_id = p_package4.Parent_ID)
WHERE p_package2.ea_GUID = '""" + SearchTerm + "' AND o.Stereotype = 'SBB_BIM_Data::fdkObjektdatentyp'"

print (sqlQueryConnector)


xmlResult = eaRep.SQLQuery(sqlQueryConnector)
from_xmlResult = XET.fromstring(xmlResult)

qwerty

  • EA Guru
  • *****
  • Posts: 13495
  • Karma: +392/-299
  • I'm no guru at all
    • View Profile
Re: SQL query of connectors and usage in Python
« Reply #4 on: February 08, 2024, 08:38:10 pm »
Save the xml result to file and have that checked with some XML tool. That should give you a hint.

q.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 12887
  • Karma: +537/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query of connectors and usage in Python
« Reply #5 on: February 08, 2024, 09:44:05 pm »
EA translates the resultset to an xml string.

Each column becomes an xml tag. The problem is that your column names are not xml safe

AS ['Physisches Anlagenobjekt'] gets translated in <'Physisches Anlagenobjekt'>, which is an invalid name for an xml node.

Simplifying the name of the columns in your query will solve the problem.

Geert

__Andre__

  • EA Novice
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Re: SQL query of connectors and usage in Python
« Reply #6 on: February 08, 2024, 09:45:28 pm »
Thanks for the hint.
The problem is that I get the error in EA. I think that EA cannot process the "code".
I only get the error from line 29 with the "call" of "AND o_LO.Name = 'Logical plant object (LO)'"

André

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 12887
  • Karma: +537/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query of connectors and usage in Python
« Reply #7 on: February 08, 2024, 10:06:03 pm »
How do you know that's the location of the error?
line 29 is part of the definition of a string. You are not even calling EA in that part of the code, and definitely not doing anything that has anything to do with xml processing.

Have you tried my suggestion?

Geert