从两个表中选择主键

Question

我有三个表：主机、服务和主机服务。你知道吗

hosts表显示了发送给每个主机的一些基本消息（示例）：

id|  host_ip  |port|server_url
1 |192.168.1.1| 80 |http://192.168.1.1:80/a/catalog.xml
2 |192.168.1.2| 80 |http://192.168.1.2:80/a/catalog.xml
3 |192.168.1.3| 80 |http://192.168.1.3:80/a/catalog.xml
4 |192.168.1.6| 80 |http://192.168.1.6:80/a/catalog.xml
5 |192.168.1.7| 80 |http://192.168.1.7:80/a/catalog.xml
6 |192.168.1.8| 80 |http://192.168.1.8:80/a/catalog.xml
7 |192.168.1.4|8080|http://192.168.1.4:8080/a/catalog.xml
8 |192.168.1.5|8080|http://192.168.1.5:8080/a/catalog.xml

服务表显示了服务的类型（示例）：

id|service_type
1 |Apache
2 |NGNIX
3 |HTTP
4 |ISO
5 |UDDC
6 |HTTPServer
7 |DAP4
8 |Ubuntu
9 |Windows10
10|WCS
11|NCSS

我从字典中将数据捕获到主机和服务：

hostServiceDict = {
"http://192.168.1.1:80/a/catalog.xml": ['Apache', 'NGNIX', 'HTTP', 'ISO'],
"http://192.168.1.2:80/a/catalog.xml": ['Apache', 'NGNIX', 'HTTP', 'ISO', 'UDDC'],
"http://192.168.1.3:80/a/catalog.xml": ['Apache', 'NGNIX', 'HTTP', 'ISO', 'HTTPServer'],
"http://192.168.1.6:80/a/catalog.xml": ['Apache', 'NGNIX', 'HTTP', 'ISO', 'DAP4'],
"http://192.168.1.7:80/a/catalog.xml": ['Apache', 'NGNIX', 'HTTP', 'ISO', 'Ubuntu', 'DAP4'],
"http://192.168.1.8:80/a/catalog.xml": ['Apache', 'NGNIX', 'HTTP', 'ISO', 'Windows10'],
"http://192.168.1.4:8080/a/catalog.xml": ['Apache', 'NGNIX', 'HTTP', 'ISO', 'Windows10'],
"http://192.168.1.5:8080/a/catalog.xml": ['Apache', 'NGNIX', 'HTTP', 'ISO', 'WCS', 'NCSS']
}

现在我想从hosts和services表中捕获id。你知道吗

"""""""""
capture data to database here
"""""""""
def capture_host_in_db(hostServiceDict):
    #(hostServiceDict)
    database = "E:\\test\database\database.sqlite"
    conn = create_connection(database)
    with conn:

        hostTemp = []
        a = []
        existingHostId = []
        for urls, services in hostServiceDict.items():
            host_port = urls.strip('http://').strip('a/catalog.xml').split(':')
            hostTemp.append(host_port[0])
            for host in hostTemp:
                """
                Check if the hosts that already in the database. If not in the database, add the hosts.
                """
                if host != select_host_by_host_ip(conn, host):
                    thredds = (host_port[0], host_port[1], urls)
                    create_unique_host(conn, thredds)
                elif host == select_host_by_host_ip(conn, host):
                    count = 1
                    ### remove duplicated hosts ###
                    a.append(host)
                    a = list(OrderedDict.fromkeys(a))

        temp = []
        for service in hostServiceDict.values():
            for i in service:
                temp.append(i)
        """
        Check if the services that the hosts have. If not in the database, add the new service in the database.
        """
        # i > theService:
        # i means all the services that are hosted per servers. theService is a unique service in database.
        for i in temp:
            theService = select_service(conn, i)
            if i != theService:
                create_unique_service(conn, i)
            elif i == theService:
                print(i)

        """
        (For host_service table)
        """
        for id in a:
            existingHostId = select_host_id_by_host_ip(conn, id)

"""""""""
database connection and SQL
"""""""""

def create_connection(db_file):
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
    return None

def select_host_by_host_ip(conn, host_ip):
    cur = conn.cursor()
    cur.execute("SELECT host_ip FROM hosts WHERE host_ip = ?", (host_ip,))
    rows = cur.fetchall()
    for i in range(len(rows)):
        aHost = rows[i][0]
        return aHost

def select_host_id_by_host_ip(conn, host_ip):
    cur = conn.cursor()
    cur.execute("SELECT DISTINCT(id) FROM hosts WHERE host_ip = ?", (host_ip,))
    rows = cur.fetchall()
    for i in range(len(rows)):
        aHostID = rows[i]
        return(aHostID)

def create_unique_host(conn, host):

    sql = ''' INSERT INTO hosts(host_ip, port, server_url)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, host)
    return cur.lastrowid

def create_unique_service(conn, service):

    sql = ''' INSERT INTO services(service_type)
              VALUES(?) '''
    cur = conn.cursor()
    cur.execute(sql, (service,))
    return cur.lastrowid


def select_service(conn, service):

    cur = conn.cursor()
    cur.execute("SELECT service_type FROM services WHERE service_type = ?", (service,))
    rows = cur.fetchall()
    for i in range(len(rows)):
        aService = rows[i][0]
        return aService

def select_service_id_by_name(conn, service):
    cur = conn.cursor()
    cur.execute("SELECT id FROM services WHERE service_type = ?", (service,))
    rows = cur.fetchall()
    for i in range(len(rows)):
        aServiceID = rows[i][0]
        return aServiceID


def create_unique_host_service(conn, host_service_by_id):
    sql = ''' INSERT INTO host_services(host_id, service_id )
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, host_service_by_id)


if __name__ == '__main__':

    capture_host_in_db(hostServiceDict)

我试过上面的代码。它们将捕获数据并将其保存到主机和服务表中。但是，在获取主机标识和服务标识方面，我只能获取主机标识，有人能帮我解决这个问题吗？你知道吗

I want the output like:
host_id|service_id
   1   |    1
   1   |    2
   1   |    3
   1   |    4
   2   |    1
   2   |    2
   2   |    3
   2   |    4
   2   |    5
   ...     ...

Answer 1

我希望您先创建服务表，然后在流的下一步中将相应的服务id添加到主机表中，总体而言，架构中缺少主键和外键组合：

    temp = []
    for service in hostServiceDict.values():
        for i in service:
            temp.append(i)
    """
    Check if the services that the hosts have. If not in the database, add the new service in the database.
    """
    # i > theService:
    # i means all the services that are hosted per servers. theService is a unique service in database.
    for i in temp:
        theService = select_service(conn, i)
        if i != theService:
            create_unique_service(conn, i)
        elif i == theService:
            print(i)

    hostTemp = []
    a = []
    existingHostId = []
    for urls, services in hostServiceDict.items():
        host_port = urls.strip('http://').strip('a/catalog.xml').split(':')
        hostTemp.append(host_port[0])
        for host in hostTemp:
            """
            Check if the hosts that already in the database. If not in the database, add the hosts.
            """
           for service in services:
            if host != select_host_by_host_ip(conn, host):
                thredds = (host_port[0], host_port[1], urls, get_service_id(conn, service))
                create_unique_host(conn, thredds)
            elif host == select_host_by_host_ip(conn, host):
                count = 1
                ### remove duplicated hosts ###
                a.append(host)
                a = list(OrderedDict.fromkeys(a))
def get_service_id(conn, service):
    cur = conn.cursor()
    cur.execute("SELECT id FROM services WHERE service_type = ?", (service,))
    row = cur.fetchone()
    return row[0]

并将create host函数更新为：

def create_unique_host(conn, host):

    sql = ''' INSERT INTO hosts(host_ip, port, server_url, service_id)
          VALUES(?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, host)
    return cur.lastrowid