Database design and python in SQLite

Question

I want to take network scan data such as hostname, IP address, ports, company name and store it in a database. I will be comparing new scan data with old, looking for variations (additions).

I am confused around the use of the primary key. I will need to tie up all the tables with the relevant data.

Is my understanding correct in that I have a PK for each table and join the tables using those PKs when doing select statements?

For instance if I want to get all the hosts and ports for CompanyA:

select CompanyA from Company, Hosts, Ports

I'm sure the syntax is incorrect however I am trying to confirm my understand of the PKs and how they work.

I didn't downvote, but I did vote to close because you are asking us for a tutorial on how to use relational databases which is "too broad" for StackOverflow. I suggest you search for "SQL tutorial" — msw
– msw, Commented Mar 8, 2016 at 23:39
I appreciate the explination, but I dont believe I was asking such a broad question. Apologies if it came across that way, refering to my response to the answer presented by J. Schneider. I was mearly trying to assertain my understanding of how I needed to structure my db and my understanding on use of the PKs and how they relate to my retrieval of the data across all tables. His response asnwered my question. — iNoob
– iNoob, Commented Mar 9, 2016 at 0:01

J. Schneider · Accepted Answer · 2016-03-08 23:42:13Z

4

Your select statement isn't getting anything right now. I assume you want to get the data from a company name "CompanyA". To do so you'd have to write

select * from Company, Hosts, Ports 
where Company.Name = "CompanyA" and Company.Company_ID = Ports.Company_ID
and Company.Company_ID = Hosts.Company_ID

The star (*) means it returns all elements, if you'd want a special element such as the Hostname you'd have to fill it in Hosts.Name.

SQL doesn't automatically join tables using a PK or any other key, you have to specifiy it in a way, such as comparing the Company_ID's as above. Without you specifying a join, it just combines every element in every table with every element in every other table. With n elements there are then n³ rows.

I would suggest for you to look at how to write SQL-statements from the start.

answered Mar 8, 2016 at 23:42

J. Schneider

1,0202 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iNoob Over a year ago

Thank you for the reply, I know my statement was incorrect. It was more of a sudo statement to help explain what I am attempting to do. My question was meant to focus on my understanding of the structure I need and how that relates to the use of the PKs. Now I believe however that your example statement explaind that for me anyway so am going to accept as the answer. again thank you for not just dismissing the question.

Collectives™ on Stack Overflow

Database design and python in SQLite

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related