Indexing with MySql
Posted by: John Smith
Date: April 04, 2011 03:55PM
Here's part of a script I'm working on below. I'm new to Python by the way:-
# Checking and creating the first table 'data' if it doesn't already exist
cursor = conn.cursor ()
cursor.execute ("DROP TABLE IF EXISTS data")
CREATE TABLE data
# Checking and creating the second table 'items' if it doesn't already exist
cursor.execute ("DROP TABLE IF EXISTS items")
CREATE TABLE data
Basically, here's what I am looking to achieve:-
My DB relates to information being passed from a local server. When the information is filtered through, I am hoping for the following:-
1.) There will be a total of two tables - the first one called "items" will just contain a list of MAC addresses and nothing else. Table two will be called "data" which will contain data piped through from a local server.
2.) I want the program to first of all check if the respective "data" table exists. If it does, then I want it to continue without creating the table. Similarly for the "items" table.
Subsequent to this, when the five pieces of data (mac, bssid, contype, ip and ltime) are fed through to the Python console from the local server, I'd like the first field, the data making up the MAC field to be checked against the data in the MAC column in the "items" table. If there is a match for a MAC address, I'd like the program to continue on to the next part of the program and populate the information into the "data" table. If there isn't a match on the MAC field, I'd like it to skip this process and move on to the next MAC address identified etc etc.... Similarly, I'm looking for only unique data to be populated into the 'data' table. So if for example, there is a match on a MAC address, the 5 pieces of information are fed into the 'data' table. If then there is another match, if the exact same information already exists, I don't want it to be stored.
I'm led to believe an index will help me but I just don't know where it would fit in. Similarly, I'm unsure as to how to program the cross-referencing / reading aspect of the data to see if it already exists.
Anyone able to look at my code and offer advice?