def login(self):
""" Логинимся в GoogleReader """
if self._loggedIn:
return True
user = settings.user()
password = settings.password()
res = True
if not user or not password:
(res, user, password) = self._getUserAndPassword(user, password)
if not res:
return False
auth = libgreader.ClientAuth(user, password)
self._reader.auth = auth
self._reader.fetchCount = 100
self._loggedIn = True
return True
def insert(inserted):
# assigning variables from inserted
global data
data = inserted[0]
single_elements = inserted[1]
mark_data = inserted[2]
mark_elements = inserted[3]
gs_bag_data = inserted[4]
gs_elements = inserted[5]
national_cor_data = inserted[6]
national_cor_elements = inserted[7]
record_attorney_data = inserted[8]
record_attorney_elements = inserted[9]
applicant_data = inserted[10]
applicant_elements = inserted[11]
nat_trade_data = inserted[12]
nat_trade_elements = inserted[13]
# connect to the postgres database
conn = psycopg2.connect(database=settings.database(),
user=settings.user(),
password=settings.password()) # Connect database
# format all the datafields
data = proof_data(data, single_elements)
mark_data = proof_data(mark_data, mark_elements)
gs_bag_data = proof_data(gs_bag_data, gs_elements)
national_cor_data = proof_data(national_cor_data, national_cor_elements)
record_attorney_data = proof_data(record_attorney_data,
record_attorney_elements)
applicant_data = proof_data(applicant_data, applicant_elements)
nat_trade_data = proof_data(nat_trade_data, nat_trade_elements)
# create a cursor which can execute postgres commands
cur = conn.cursor() # Initiate the cursor which executes postgres commands
# cur.execute('''drop table if exists ''' + table_out +';') # Remove old table
# make lawyer table
print data['ApplicationNumberText']
# create lawyer table
cur.execute('''CREATE TABLE IF NOT EXISTS lawyer
(
id SERIAL PRIMARY KEY,
attorney_name TEXT,
attorney_comment TEXT,
contact_name TEXT,
organization_standard_name TEXT,
address_line_text TEXT,
address_line_text_2 TEXT,
city_name TEXT,
geographic_region_name VARCHAR(25),
country_code VARCHAR(25),
postal_code VARCHAR(25),
email_address_text TEXT,
phone_number VARCHAR(25),
fax_number VARCHAR(25),
website TEXT
);''')
# insert into lawyer table
cur.execute(
'''INSERT INTO lawyer
(
attorney_name,
attorney_comment,
contact_name,
organization_standard_name,
address_line_text,
address_line_text_2,
city_name,
geographic_region_name,
country_code,
postal_code,
email_address_text,
phone_number,
fax_number,
website
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT email_address_text
FROM lawyer
WHERE email_address_text LIKE %s
)''', (record_attorney_data['PersonFullName'],
record_attorney_data['CommentText'],
national_cor_data['PersonFullName'],
national_cor_data['OrganizationStandardName'],
national_cor_data['AddressLineText'],
national_cor_data['AddressLineText2'], national_cor_data['CityName'],
national_cor_data['GeographicRegionName'],
national_cor_data['CountryCode'], national_cor_data['PostalCode'],
national_cor_data['EmailAddressText'],
national_cor_data['PhoneNumber'], national_cor_data['FaxNumber'],
get_website(national_cor_data['EmailAddressText']),
national_cor_data['EmailAddressText']))
# create trademark table
cur.execute('''CREATE TABLE IF NOT EXISTS trademark
(
id SERIAL PRIMARY KEY,
serial_number INTEGER,
registration_office_code VARCHAR(5),
ip_office_code VARCHAR(5),
registration_number VARCHAR(20),
application_date DATE,
registration_date DATE,
filing_place VARCHAR(5),
mark_current_status_date DATE,
mark_category VARCHAR(25),
mark_feature_category VARCHAR(100),
first_used_date DATE,
blank_month_1 BOOLEAN,
blank_day_1 BOOLEAN,
first_used_commerce_date DATE,
blank_month_2 BOOLEAN,
blank_day_2 BOOLEAN,
publication_identifier VARCHAR(100),
publication_date DATE,
class_number VARCHAR(5),
goods_services_description_text TEXT,
national_status_category TEXT,
national_status_code TEXT,
national_status_external_description_text TEXT
);''')
# insert into trademark table
cur.execute(
'''INSERT INTO trademark
(
serial_number,
registration_office_code,
IP_office_code,
registration_number,
application_date,
registration_date,
filing_place,
mark_current_status_date,
mark_category,
mark_feature_category,
first_used_date,
blank_month_1,
blank_day_1,
first_used_commerce_date,
blank_month_2,
blank_day_2,
publication_identifier,
publication_date,
class_number,
goods_services_description_text,
national_status_category,
national_status_code,
national_status_external_description_text
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s
FROM lawyer
WHERE lawyer.email_address_text LIKE %s
AND NOT EXISTS
(
SELECT serial_number
FROM trademark
WHERE serial_number = %s
)''',
(data['ApplicationNumberText'], data['RegistrationOfficeCode'],
data['IPOfficeCode'], data['RegistrationNumber'], get_date(data),
data['RegistrationDate'], data['FilingPlace'],
data['MarkCurrentStatusDate'], data['MarkCategory'],
data['MarkFeatureCategory'], data['FirstUsedDate'],
data['BlankMonth1'], data['BlankDay1'], data['FirstUsedCommerceDate'],
data['BlankMonth2'], data['BlankDay2'], data['PublicationIdentifier'],
data['PublicationDate'], data['ClassNumber'],
data['GoodsServicesDescriptionText'], data['NationalStatusCategory'],
data['NationalStatusCode'],
data['NationalStatusExternalDescriptionText'],
national_cor_data['EmailAddressText'], data['ApplicationNumberText']))
# create word_mark table
cur.execute('''CREATE TABLE IF NOT EXISTS word_mark
(
id SERIAL PRIMARY KEY,
mark_verbal_element_text VARCHAR(1000),
mark_significant_verbal_element_text VARCHAR(1000),
mark_standard_character_indicator BOOLEAN
); ''')
# insert into word_mark table
if (data['MarkVerbalElementText'] is None):
data['MarkVerbalElementText'] = 'No word_mark available'
cur.execute(
'''INSERT INTO word_mark
(
mark_verbal_element_text,
mark_significant_verbal_element_text,
mark_standard_character_indicator
)
SELECT %s, %s, %s
WHERE NOT EXISTS
(
SELECT mark_verbal_element_text
FROM word_mark
WHERE mark_verbal_element_text = %s
)''', (data['MarkVerbalElementText'],
data['MarkSignificantVerbalElementText'],
data['MarkStandardCharacterIndicator'],
data['MarkVerbalElementText']))
# create image_mark table
cur.execute('''CREATE TABLE IF NOT EXISTS image_mark
(
id SERIAL PRIMARY KEY,
image_file_name VARCHAR(1000),
mark_image_colour_claimed_text TEXT,
mark_image_colour_part_claimed_text TEXT,
image_colour_indicator BOOLEAN
);''')
# insert into image_mark table
if (data['MarkVerbalElementText'] is None):
image_file = 'No image available'
else:
image_file = data['ApplicationNumberText'] + '.png'
cur.execute(
'''INSERT INTO image_mark
(
image_file_name,
mark_image_colour_claimed_text,
mark_image_colour_part_claimed_text,
image_colour_indicator
)
SELECT %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT image_file_name
FROM image_mark
WHERE image_file_name = %s
)''', (image_file, data['MarkImageColourClaimedText'],
data['MarkImageColourPartClaimedText'], data['ImageColourIndicator'],
image_file))
# create sound_mark table
cur.execute('''CREATE TABLE IF NOT EXISTS sound_mark
(
id SERIAL PRIMARY KEY,
mark_sound VARCHAR(1000)
);''')
# insert into sound_mark table
if (data['MarkSound'] is None):
data['MarkSound'] = 'No sound_mark available'
cur.execute(
'''INSERT INTO sound_mark
(
mark_sound
)
SELECT %s
WHERE NOT EXISTS
(
SELECT mark_sound
FROM sound_mark
WHERE mark_sound = %s
)''', (data['MarkSound'], data['MarkSound']))
# create current_basis table
cur.execute('''CREATE TABLE IF NOT EXISTS current_basis
(
id SERIAL PRIMARY KEY,
basis_foreign_application_indicator BOOLEAN,
basis_foreign_registration_indicator BOOLEAN,
basis_use_indicator BOOLEAN,
basis_intent_to_use_indicator BOOLEAN,
no_basis_indicator BOOLEAN
);''')
# insert into current_basis table
cur.execute(
'''INSERT INTO current_basis
(
basis_foreign_application_indicator,
basis_foreign_registration_indicator,
basis_use_indicator,
basis_intent_to_use_indicator,
no_basis_indicator
)
SELECT %s, %s, %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
basis_foreign_application_indicator,
basis_foreign_registration_indicator,
basis_use_indicator,
basis_intent_to_use_indicator,
no_basis_indicator
)
FROM current_basis
WHERE basis_foreign_application_indicator = %s
AND basis_use_indicator = %s
AND basis_intent_to_use_indicator = %s
AND basis_foreign_registration_indicator = %s
AND no_basis_indicator = %s
)''', (data['BasisForeignApplicationIndicator'],
data['BasisForeignRegistrationIndicator'], data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'], data['NoBasisIndicator'],
data['ApplicationNumberText'],
data['BasisForeignApplicationIndicator'], data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['BasisForeignRegistrationIndicator'], data['NoBasisIndicator']))
# create mark event table
cur.execute('''CREATE TABLE IF NOT EXISTS mark_event
(
id SERIAL PRIMARY KEY,
mark_event_category TEXT,
mark_event_code TEXT,
mark_event_description_text TEXT,
mark_event_entry_number INTEGER,
mark_event_additional_text TEXT,
mark_event_date DATE
);''')
# insert into mark event table
for i in range(len(mark_data.values()[0])):
cur.execute(
'''INSERT INTO mark_event
(
mark_event_category,
mark_event_code,
mark_event_description_text,
mark_event_entry_number,
mark_event_additional_text,
mark_event_date
)
SELECT %s, %s, %s, %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
mark_event_category,
mark_event_code,
mark_event_description_text,
mark_event_entry_number,
mark_event_additional_text
)
FROM mark_event
WHERE mark_event_category LIKE %s
AND mark_event_code LIKE %s
AND mark_event_description_text LIKE %s
AND mark_event_entry_number = %s
AND mark_event_additional_text LIKE %s
)''', (mark_data['MarkEventCategory'][i], mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i],
mark_data['MarkEventDate'][i], data['ApplicationNumberText'],
mark_data['MarkEventCategory'][i], mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i]))
# create goods and services table
cur.execute('''CREATE TABLE IF NOT EXISTS gs_bag
(
id SERIAL PRIMARY KEY,
classification_kind_code VARCHAR(25),
class_number INTEGER,
national_class_number INTEGER
);''')
# insert into goods and services table
for i in range(len(gs_bag_data.values()[0])):
cur.execute(
'''INSERT INTO gs_bag
(
classification_kind_code,
class_number,
national_class_number
)
SELECT %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
classification_kind_code,
class_number,
national_class_number
)
FROM gs_bag
WHERE classification_kind_code LIKE %s
AND (class_number = %s OR national_class_number = %s)
)''', (gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i],
data['ApplicationNumberText'],
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i]))
# create applicant table
cur.execute('''CREATE TABLE IF NOT EXISTS applicant
(
id SERIAL PRIMARY KEY,
legal_entity_name TEXT,
national_legal_entity_code TEXT,
incorporation_country_code VARCHAR(25),
incorporation_state VARCHAR(25),
organization_standard_name TEXT,
entity_name TEXT,
entity_name_category TEXT,
address_line_text TEXT,
address_line_text_2 TEXT,
city_name TEXT,
geographic_region_name TEXT,
country_code VARCHAR(25),
postal_code VARCHAR(25)
);''')
# insert into applicant table
for i in range(len(applicant_data.values()[0])):
cur.execute(
'''INSERT INTO applicant
(
legal_entity_name,
national_legal_entity_code,
incorporation_country_code,
incorporation_state,
organization_standard_name,
entity_name,
entity_name_category,
address_line_text,
address_line_text_2,
city_name,
geographic_region_name,
country_code,
postal_code
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT
(
legal_entity_name,
incorporation_country_code,
incorporation_state,
organization_standard_name,
entity_name
)
FROM applicant
WHERE legal_entity_name LIKE %s
AND incorporation_country_code LIKE %s
AND incorporation_state LIKE %s
AND organization_standard_name LIKE %s
AND entity_name LIKE %s
)''', (applicant_data['LegalEntityName'][i],
applicant_data['NationalLegalEntityCode'][i],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i],
applicant_data['EntityNameCategory'][i],
applicant_data['AddressLineText'][i],
applicant_data['AddressLineText2'][i],
applicant_data['CityName'][i],
applicant_data['GeographicRegionName'][i],
applicant_data['CountryCode'][i], applicant_data['PostalCode'][i],
applicant_data['LegalEntityName'][i],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i]))
# create national trademark information table
cur.execute('''CREATE TABLE IF NOT EXISTS national_trademark
(
id SERIAL PRIMARY KEY,
register_category TEXT,
amended_principal_register_indicator BOOLEAN,
amended_supplemental_register_indicator BOOLEAN,
mark_current_status_external_description_text TEXT
); ''')
# insert into national trademark information table
cur.execute(
'''INSERT INTO national_trademark
(
register_category,
amended_principal_register_indicator,
amended_supplemental_register_indicator,
mark_current_status_external_description_text
)
SELECT %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT
(
register_category,
amended_principal_register_indicator,
amended_supplemental_register_indicator,
mark_current_status_external_description_text
)
FROM national_trademark
WHERE register_category LIKE %s
AND amended_principal_register_indicator = %s
AND amended_supplemental_register_indicator = %s
AND mark_current_status_external_description_text LIKE %s
)''', (nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText'],
nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText']))
# all join tables connecting every table to trademarks table
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# create trademark_word_mark table
# connects trademark to word_mark
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_word_mark
(
trademark_id INTEGER,
word_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT word_mark_id_fk
FOREIGN KEY (word_mark_id)
REFERENCES word_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_word_mark
cur.execute(
'''INSERT INTO trademark_word_mark
(
trademark_id,
word_mark_id
)
SELECT trademark.id, word_mark.id
FROM trademark
CROSS JOIN word_mark
WHERE serial_number = %s
AND mark_verbal_element_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
word_mark_id
)
FROM trademark_word_mark
WHERE trademark_id = trademark.id
AND word_mark_id = word_mark.id
)''', (data['ApplicationNumberText'], data['MarkVerbalElementText']))
# note, the argument list must always be a LIST (even single tuple)!
# id FROM trademark WHERE serial_number = %s
# create trademark_image_mark table
# connects trademark to image_mark
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_image_mark
(
trademark_id INTEGER,
image_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT image_mark_id_fk
FOREIGN KEY (image_mark_id)
REFERENCES image_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_image_mark
cur.execute(
'''INSERT INTO trademark_image_mark
(
trademark_id,
image_mark_id
)
SELECT trademark.id, image_mark.id
FROM trademark
CROSS JOIN image_mark
WHERE serial_number = %s
AND image_file_name LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
image_mark_id
)
FROM trademark_image_mark
WHERE trademark_id = trademark.id
AND image_mark_id = image_mark.id
)''', (data['ApplicationNumberText'], image_file))
# create trademark_sound_mark table
# connects trademark to sound_mark
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_sound_mark
(
trademark_id INTEGER,
sound_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT sound_mark_id_fk
FOREIGN KEY (sound_mark_id)
REFERENCES sound_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_sound_mark
cur.execute(
'''INSERT INTO trademark_sound_mark
(
trademark_id,
sound_mark_id
)
SELECT trademark.id, sound_mark.id
FROM trademark
CROSS JOIN sound_mark
WHERE serial_number = %s
AND mark_sound LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
sound_mark_id
)
FROM trademark_sound_mark
WHERE trademark_id = trademark.id
AND sound_mark_id = sound_mark.id
)''', (data['ApplicationNumberText'], data['MarkSound']))
# create trademark_lawyer table
# connects trademark to lawyer
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_lawyer
(
trademark_id INTEGER,
lawyer_id INTEGER,
application_date DATE,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT lawyer_id_fk
FOREIGN KEY (lawyer_id)
REFERENCES lawyer (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_lawyer
cur.execute(
'''INSERT INTO trademark_lawyer
(
trademark_id,
lawyer_id,
application_date
)
SELECT trademark.id, lawyer.id, %s
FROM trademark
CROSS JOIN lawyer
WHERE serial_number = %s
AND email_address_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
lawyer_id,
application_date
)
FROM trademark_lawyer
WHERE trademark_id = trademark.id
AND lawyer_id = lawyer.id
AND application_date = %s
);''', (get_date(data), data['ApplicationNumberText'],
national_cor_data['EmailAddressText'], get_date(data)))
# create trademark_current_basis table
# connects trademark to current_basis
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_current_basis
(
trademark_id INTEGER,
current_basis_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT current_basis_id_fk
FOREIGN KEY (current_basis_id)
REFERENCES current_basis (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_current_basis
cur.execute(
'''INSERT INTO trademark_current_basis
(
trademark_id,
current_basis_id
)
SELECT trademark.id, current_basis.id
FROM trademark
CROSS JOIN current_basis
WHERE serial_number = %s
AND basis_foreign_application_indicator = %s
AND basis_use_indicator = %s
AND basis_intent_to_use_indicator = %s
AND basis_foreign_registration_indicator = %s
AND no_basis_indicator = %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
current_basis_id
)
FROM trademark_current_basis
WHERE trademark_id = trademark.id
AND current_basis_id = current_basis.id
)''', (data['ApplicationNumberText'],
data['BasisForeignApplicationIndicator'], data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['BasisForeignRegistrationIndicator'], data['NoBasisIndicator']))
# create trademark_mark_event table
# connects trademark to mark_event
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_mark_event
(
trademark_id INTEGER,
mark_event_id INTEGER,
mark_event_date DATE,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT mark_event_id_fk
FOREIGN KEY (mark_event_id)
REFERENCES mark_event (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_mark_event
for i in range(len(mark_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_mark_event
(
trademark_id,
mark_event_id,
mark_event_date
)
SELECT trademark.id, mark_event.id, %s
FROM trademark
CROSS JOIN mark_event
WHERE serial_number = %s
AND mark_event_category LIKE %s
AND mark_event_code LIKE %s
AND mark_event_description_text LIKE %s
AND mark_event_entry_number = %s
AND mark_event_additional_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
mark_event_id,
mark_event_date
)
FROM trademark_mark_event
WHERE trademark_id = trademark.id
AND mark_event_id = mark_event.id
AND mark_event_date = %s
)''', (mark_data['MarkEventDate'][i], data['ApplicationNumberText'],
mark_data['MarkEventCategory'][i], mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i],
mark_data['MarkEventDate'][i]))
# create trademark_gs_bag table
# connects trademark to gs_bag
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_gs_bag
(
trademark_id INTEGER,
gs_bag_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT gs_bag_id_fk
FOREIGN KEY (gs_bag_id)
REFERENCES gs_bag (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_gs_bag
for i in range(len(gs_bag_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_gs_bag
(
trademark_id,
gs_bag_id
)
SELECT trademark.id, gs_bag.id
FROM trademark
CROSS JOIN gs_bag
WHERE serial_number = %s
AND classification_kind_code LIKE %s
AND (gs_bag.class_number = %s OR national_class_number = %s)
AND NOT EXISTS
(
SELECT
(
trademark_id,
gs_bag_id
)
FROM trademark_gs_bag
WHERE trademark_id = trademark.id
AND gs_bag_id = gs_bag.id
)''', (data['ApplicationNumberText'],
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i]))
# create trademark_applicant table
# connects trademark to applicant
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_applicant
(
trademark_id INTEGER,
applicant_id INTEGER,
applicant_role TEXT,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT applicant_id_fk
FOREIGN KEY (applicant_id)
REFERENCES applicant (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_applicant
for i in range(len(applicant_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_applicant
(
trademark_id,
applicant_id,
applicant_role
)
SELECT trademark.id, applicant.id, %s
FROM trademark
CROSS JOIN applicant
WHERE serial_number = %s
AND incorporation_country_code LIKE %s
AND incorporation_state LIKE %s
AND organization_standard_name LIKE %s
AND entity_name LIKE %s
AND postal_code LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
applicant_id,
applicant_role
)
FROM trademark_applicant
WHERE trademark_id = trademark.id
AND applicant_id = applicant.id
AND applicant_role = %s
)''', (applicant_data['CommentText'][i], data['ApplicationNumberText'],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i], applicant_data['PostalCode'][i],
applicant_data['CommentText'][i]))
# create trademark_national_trademark table
# connects trademark to nationa_trademark
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_national_trademark
(
trademark_id INTEGER,
national_trademark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT national_trademark_id_fk
FOREIGN KEY (national_trademark_id)
REFERENCES national_trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_national_trademark
cur.execute(
'''INSERT INTO trademark_national_trademark
(
trademark_id,
national_trademark_id
)
SELECT trademark.id, national_trademark.id
FROM trademark
CROSS JOIN national_trademark
WHERE serial_number = %s
AND register_category LIKE %s
AND amended_principal_register_indicator = %s
AND amended_supplemental_register_indicator = %s
AND mark_current_status_external_description_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
national_trademark_id
)
FROM trademark_national_trademark
WHERE trademark_id = trademark.id
AND national_trademark_id = national_trademark.id
)''', (data['ApplicationNumberText'], nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText']))
conn.commit() # validate all changes and commit them to postgres
conn.close()
def insert(inserted):
# assigning variables from inserted
global data
data = inserted[0]
single_elements = inserted[1]
mark_data = inserted[2]
mark_elements = inserted[3]
gs_bag_data = inserted[4]
gs_elements = inserted[5]
national_cor_data = inserted[6]
national_cor_elements = inserted[7]
record_attorney_data = inserted[8]
record_attorney_elements = inserted[9]
applicant_data = inserted[10]
applicant_elements = inserted[11]
nat_trade_data = inserted[12]
nat_trade_elements = inserted[13]
# connect to the postgres database
conn = psycopg2.connect(database = settings.database(),
user = settings.user(),
password = settings.password()) # Connect database
# format all the datafields
data = proof_data(data, single_elements)
mark_data = proof_data(mark_data, mark_elements)
gs_bag_data = proof_data(gs_bag_data, gs_elements)
national_cor_data = proof_data(national_cor_data, national_cor_elements)
record_attorney_data = proof_data(record_attorney_data,
record_attorney_elements)
applicant_data = proof_data(applicant_data, applicant_elements)
nat_trade_data = proof_data(nat_trade_data, nat_trade_elements)
# create a cursor which can execute postgres commands
cur = conn.cursor() # Initiate the cursor which executes postgres commands
# cur.execute('''drop table if exists ''' + table_out +';') # Remove old table
# make lawyer table
print data['ApplicationNumberText']
# create lawyer table
cur.execute(
'''CREATE TABLE IF NOT EXISTS lawyer
(
id SERIAL PRIMARY KEY,
attorney_name TEXT,
attorney_comment TEXT,
contact_name TEXT,
organization_standard_name TEXT,
address_line_text TEXT,
address_line_text_2 TEXT,
city_name TEXT,
geographic_region_name VARCHAR(25),
country_code VARCHAR(25),
postal_code VARCHAR(25),
email_address_text TEXT,
phone_number VARCHAR(25),
fax_number VARCHAR(25),
website TEXT
);'''
)
# insert into lawyer table
cur.execute(
'''INSERT INTO lawyer
(
attorney_name,
attorney_comment,
contact_name,
organization_standard_name,
address_line_text,
address_line_text_2,
city_name,
geographic_region_name,
country_code,
postal_code,
email_address_text,
phone_number,
fax_number,
website
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT email_address_text
FROM lawyer
WHERE email_address_text LIKE %s
)''',
(
record_attorney_data['PersonFullName'],
record_attorney_data['CommentText'],
national_cor_data['PersonFullName'],
national_cor_data['OrganizationStandardName'],
national_cor_data['AddressLineText'],
national_cor_data['AddressLineText2'],
national_cor_data['CityName'],
national_cor_data['GeographicRegionName'],
national_cor_data['CountryCode'],
national_cor_data['PostalCode'],
national_cor_data['EmailAddressText'],
national_cor_data['PhoneNumber'],
national_cor_data['FaxNumber'],
get_website(national_cor_data['EmailAddressText']),
national_cor_data['EmailAddressText']
)
)
# create trademark table
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark
(
id SERIAL PRIMARY KEY,
serial_number INTEGER,
registration_office_code VARCHAR(5),
ip_office_code VARCHAR(5),
registration_number VARCHAR(20),
application_date DATE,
registration_date DATE,
filing_place VARCHAR(5),
mark_current_status_date DATE,
mark_category VARCHAR(25),
mark_feature_category VARCHAR(100),
first_used_date DATE,
blank_month_1 BOOLEAN,
blank_day_1 BOOLEAN,
first_used_commerce_date DATE,
blank_month_2 BOOLEAN,
blank_day_2 BOOLEAN,
publication_identifier VARCHAR(100),
publication_date DATE,
class_number VARCHAR(5),
goods_services_description_text TEXT,
national_status_category TEXT,
national_status_code TEXT,
national_status_external_description_text TEXT
);'''
)
# insert into trademark table
cur.execute(
'''INSERT INTO trademark
(
serial_number,
registration_office_code,
IP_office_code,
registration_number,
application_date,
registration_date,
filing_place,
mark_current_status_date,
mark_category,
mark_feature_category,
first_used_date,
blank_month_1,
blank_day_1,
first_used_commerce_date,
blank_month_2,
blank_day_2,
publication_identifier,
publication_date,
class_number,
goods_services_description_text,
national_status_category,
national_status_code,
national_status_external_description_text
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s
FROM lawyer
WHERE lawyer.email_address_text LIKE %s
AND NOT EXISTS
(
SELECT serial_number
FROM trademark
WHERE serial_number = %s
)''',
(
data['ApplicationNumberText'],
data['RegistrationOfficeCode'],
data['IPOfficeCode'],
data['RegistrationNumber'],
get_date(data),
data['RegistrationDate'],
data['FilingPlace'],
data['MarkCurrentStatusDate'],
data['MarkCategory'],
data['MarkFeatureCategory'],
data['FirstUsedDate'],
data['BlankMonth1'],
data['BlankDay1'],
data['FirstUsedCommerceDate'],
data['BlankMonth2'],
data['BlankDay2'],
data['PublicationIdentifier'],
data['PublicationDate'],
data['ClassNumber'],
data['GoodsServicesDescriptionText'],
data['NationalStatusCategory'],
data['NationalStatusCode'],
data['NationalStatusExternalDescriptionText'],
national_cor_data['EmailAddressText'],
data['ApplicationNumberText']
)
)
# create word_mark table
cur.execute(
'''CREATE TABLE IF NOT EXISTS word_mark
(
id SERIAL PRIMARY KEY,
mark_verbal_element_text VARCHAR(1000),
mark_significant_verbal_element_text VARCHAR(1000),
mark_standard_character_indicator BOOLEAN
); '''
)
# insert into word_mark table
if (data['MarkVerbalElementText'] is None):
data['MarkVerbalElementText'] = 'No word_mark available'
cur.execute(
'''INSERT INTO word_mark
(
mark_verbal_element_text,
mark_significant_verbal_element_text,
mark_standard_character_indicator
)
SELECT %s, %s, %s
WHERE NOT EXISTS
(
SELECT mark_verbal_element_text
FROM word_mark
WHERE mark_verbal_element_text = %s
)''',
(
data['MarkVerbalElementText'],
data['MarkSignificantVerbalElementText'],
data['MarkStandardCharacterIndicator'],
data['MarkVerbalElementText']
)
)
# create image_mark table
cur.execute(
'''CREATE TABLE IF NOT EXISTS image_mark
(
id SERIAL PRIMARY KEY,
image_file_name VARCHAR(1000),
mark_image_colour_claimed_text TEXT,
mark_image_colour_part_claimed_text TEXT,
image_colour_indicator BOOLEAN
);'''
)
# insert into image_mark table
if (data['MarkVerbalElementText'] is None):
image_file = 'No image available'
else:
image_file = data['ApplicationNumberText'] + '.png'
cur.execute(
'''INSERT INTO image_mark
(
image_file_name,
mark_image_colour_claimed_text,
mark_image_colour_part_claimed_text,
image_colour_indicator
)
SELECT %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT image_file_name
FROM image_mark
WHERE image_file_name = %s
)''',
(
image_file,
data['MarkImageColourClaimedText'],
data['MarkImageColourPartClaimedText'],
data['ImageColourIndicator'],
image_file
)
)
# create sound_mark table
cur.execute(
'''CREATE TABLE IF NOT EXISTS sound_mark
(
id SERIAL PRIMARY KEY,
mark_sound VARCHAR(1000)
);'''
)
# insert into sound_mark table
if (data['MarkSound'] is None):
data['MarkSound'] = 'No sound_mark available'
cur.execute(
'''INSERT INTO sound_mark
(
mark_sound
)
SELECT %s
WHERE NOT EXISTS
(
SELECT mark_sound
FROM sound_mark
WHERE mark_sound = %s
)''',
(
data['MarkSound'],
data['MarkSound']
)
)
# create current_basis table
cur.execute(
'''CREATE TABLE IF NOT EXISTS current_basis
(
id SERIAL PRIMARY KEY,
basis_foreign_application_indicator BOOLEAN,
basis_foreign_registration_indicator BOOLEAN,
basis_use_indicator BOOLEAN,
basis_intent_to_use_indicator BOOLEAN,
no_basis_indicator BOOLEAN
);'''
)
# insert into current_basis table
cur.execute(
'''INSERT INTO current_basis
(
basis_foreign_application_indicator,
basis_foreign_registration_indicator,
basis_use_indicator,
basis_intent_to_use_indicator,
no_basis_indicator
)
SELECT %s, %s, %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
basis_foreign_application_indicator,
basis_foreign_registration_indicator,
basis_use_indicator,
basis_intent_to_use_indicator,
no_basis_indicator
)
FROM current_basis
WHERE basis_foreign_application_indicator = %s
AND basis_use_indicator = %s
AND basis_intent_to_use_indicator = %s
AND basis_foreign_registration_indicator = %s
AND no_basis_indicator = %s
)''',
(
data['BasisForeignApplicationIndicator'],
data['BasisForeignRegistrationIndicator'],
data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['NoBasisIndicator'],
data['ApplicationNumberText'],
data['BasisForeignApplicationIndicator'],
data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['BasisForeignRegistrationIndicator'],
data['NoBasisIndicator']
)
)
# create mark event table
cur.execute(
'''CREATE TABLE IF NOT EXISTS mark_event
(
id SERIAL PRIMARY KEY,
mark_event_category TEXT,
mark_event_code TEXT,
mark_event_description_text TEXT,
mark_event_entry_number INTEGER,
mark_event_additional_text TEXT,
mark_event_date DATE
);'''
)
# insert into mark event table
for i in range(len(mark_data.values()[0])):
cur.execute(
'''INSERT INTO mark_event
(
mark_event_category,
mark_event_code,
mark_event_description_text,
mark_event_entry_number,
mark_event_additional_text,
mark_event_date
)
SELECT %s, %s, %s, %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
mark_event_category,
mark_event_code,
mark_event_description_text,
mark_event_entry_number,
mark_event_additional_text
)
FROM mark_event
WHERE mark_event_category LIKE %s
AND mark_event_code LIKE %s
AND mark_event_description_text LIKE %s
AND mark_event_entry_number = %s
AND mark_event_additional_text LIKE %s
)''',
(
mark_data['MarkEventCategory'][i],
mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i],
mark_data['MarkEventDate'][i],
data['ApplicationNumberText'],
mark_data['MarkEventCategory'][i],
mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i]
)
)
# create goods and services table
cur.execute(
'''CREATE TABLE IF NOT EXISTS gs_bag
(
id SERIAL PRIMARY KEY,
classification_kind_code VARCHAR(25),
class_number INTEGER,
national_class_number INTEGER
);'''
)
# insert into goods and services table
for i in range(len(gs_bag_data.values()[0])):
cur.execute(
'''INSERT INTO gs_bag
(
classification_kind_code,
class_number,
national_class_number
)
SELECT %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
classification_kind_code,
class_number,
national_class_number
)
FROM gs_bag
WHERE classification_kind_code LIKE %s
AND (class_number = %s OR national_class_number = %s)
)''',
(
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i],
data['ApplicationNumberText'],
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i]
)
)
# create applicant table
cur.execute(
'''CREATE TABLE IF NOT EXISTS applicant
(
id SERIAL PRIMARY KEY,
legal_entity_name TEXT,
national_legal_entity_code TEXT,
incorporation_country_code VARCHAR(25),
incorporation_state VARCHAR(25),
organization_standard_name TEXT,
entity_name TEXT,
entity_name_category TEXT,
address_line_text TEXT,
address_line_text_2 TEXT,
city_name TEXT,
geographic_region_name TEXT,
country_code VARCHAR(25),
postal_code VARCHAR(25)
);'''
)
# insert into applicant table
for i in range(len(applicant_data.values()[0])):
cur.execute(
'''INSERT INTO applicant
(
legal_entity_name,
national_legal_entity_code,
incorporation_country_code,
incorporation_state,
organization_standard_name,
entity_name,
entity_name_category,
address_line_text,
address_line_text_2,
city_name,
geographic_region_name,
country_code,
postal_code
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT
(
legal_entity_name,
incorporation_country_code,
incorporation_state,
organization_standard_name,
entity_name
)
FROM applicant
WHERE legal_entity_name LIKE %s
AND incorporation_country_code LIKE %s
AND incorporation_state LIKE %s
AND organization_standard_name LIKE %s
AND entity_name LIKE %s
)''',
(
applicant_data['LegalEntityName'][i],
applicant_data['NationalLegalEntityCode'][i],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i],
applicant_data['EntityNameCategory'][i],
applicant_data['AddressLineText'][i],
applicant_data['AddressLineText2'][i],
applicant_data['CityName'][i],
applicant_data['GeographicRegionName'][i],
applicant_data['CountryCode'][i],
applicant_data['PostalCode'][i],
applicant_data['LegalEntityName'][i],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i]
)
)
# create national trademark information table
cur.execute(
'''CREATE TABLE IF NOT EXISTS national_trademark
(
id SERIAL PRIMARY KEY,
register_category TEXT,
amended_principal_register_indicator BOOLEAN,
amended_supplemental_register_indicator BOOLEAN,
mark_current_status_external_description_text TEXT
); '''
)
# insert into national trademark information table
cur.execute(
'''INSERT INTO national_trademark
(
register_category,
amended_principal_register_indicator,
amended_supplemental_register_indicator,
mark_current_status_external_description_text
)
SELECT %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT
(
register_category,
amended_principal_register_indicator,
amended_supplemental_register_indicator,
mark_current_status_external_description_text
)
FROM national_trademark
WHERE register_category LIKE %s
AND amended_principal_register_indicator = %s
AND amended_supplemental_register_indicator = %s
AND mark_current_status_external_description_text LIKE %s
)''',
(
nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText'],
nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText']
)
)
# all join tables connecting every table to trademarks table
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# create trademark_word_mark table
# connects trademark to word_mark
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_word_mark
(
trademark_id INTEGER,
word_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT word_mark_id_fk
FOREIGN KEY (word_mark_id)
REFERENCES word_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_word_mark
cur.execute(
'''INSERT INTO trademark_word_mark
(
trademark_id,
word_mark_id
)
SELECT trademark.id, word_mark.id
FROM trademark
CROSS JOIN word_mark
WHERE serial_number = %s
AND mark_verbal_element_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
word_mark_id
)
FROM trademark_word_mark
WHERE trademark_id = trademark.id
AND word_mark_id = word_mark.id
)''',
(
data['ApplicationNumberText'],
data['MarkVerbalElementText']
)
)
# note, the argument list must always be a LIST (even single tuple)!
# id FROM trademark WHERE serial_number = %s
# create trademark_image_mark table
# connects trademark to image_mark
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_image_mark
(
trademark_id INTEGER,
image_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT image_mark_id_fk
FOREIGN KEY (image_mark_id)
REFERENCES image_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_image_mark
cur.execute(
'''INSERT INTO trademark_image_mark
(
trademark_id,
image_mark_id
)
SELECT trademark.id, image_mark.id
FROM trademark
CROSS JOIN image_mark
WHERE serial_number = %s
AND image_file_name LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
image_mark_id
)
FROM trademark_image_mark
WHERE trademark_id = trademark.id
AND image_mark_id = image_mark.id
)''',
(
data['ApplicationNumberText'],
image_file
)
)
# create trademark_sound_mark table
# connects trademark to sound_mark
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_sound_mark
(
trademark_id INTEGER,
sound_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT sound_mark_id_fk
FOREIGN KEY (sound_mark_id)
REFERENCES sound_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_sound_mark
cur.execute(
'''INSERT INTO trademark_sound_mark
(
trademark_id,
sound_mark_id
)
SELECT trademark.id, sound_mark.id
FROM trademark
CROSS JOIN sound_mark
WHERE serial_number = %s
AND mark_sound LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
sound_mark_id
)
FROM trademark_sound_mark
WHERE trademark_id = trademark.id
AND sound_mark_id = sound_mark.id
)''',
(
data['ApplicationNumberText'],
data['MarkSound']
)
)
# create trademark_lawyer table
# connects trademark to lawyer
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_lawyer
(
trademark_id INTEGER,
lawyer_id INTEGER,
application_date DATE,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT lawyer_id_fk
FOREIGN KEY (lawyer_id)
REFERENCES lawyer (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_lawyer
cur.execute(
'''INSERT INTO trademark_lawyer
(
trademark_id,
lawyer_id,
application_date
)
SELECT trademark.id, lawyer.id, %s
FROM trademark
CROSS JOIN lawyer
WHERE serial_number = %s
AND email_address_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
lawyer_id,
application_date
)
FROM trademark_lawyer
WHERE trademark_id = trademark.id
AND lawyer_id = lawyer.id
AND application_date = %s
);''',
(
get_date(data),
data['ApplicationNumberText'],
national_cor_data['EmailAddressText'],
get_date(data)
)
)
# create trademark_current_basis table
# connects trademark to current_basis
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_current_basis
(
trademark_id INTEGER,
current_basis_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT current_basis_id_fk
FOREIGN KEY (current_basis_id)
REFERENCES current_basis (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_current_basis
cur.execute(
'''INSERT INTO trademark_current_basis
(
trademark_id,
current_basis_id
)
SELECT trademark.id, current_basis.id
FROM trademark
CROSS JOIN current_basis
WHERE serial_number = %s
AND basis_foreign_application_indicator = %s
AND basis_use_indicator = %s
AND basis_intent_to_use_indicator = %s
AND basis_foreign_registration_indicator = %s
AND no_basis_indicator = %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
current_basis_id
)
FROM trademark_current_basis
WHERE trademark_id = trademark.id
AND current_basis_id = current_basis.id
)''',
(
data['ApplicationNumberText'],
data['BasisForeignApplicationIndicator'],
data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['BasisForeignRegistrationIndicator'],
data['NoBasisIndicator']
)
)
# create trademark_mark_event table
# connects trademark to mark_event
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_mark_event
(
trademark_id INTEGER,
mark_event_id INTEGER,
mark_event_date DATE,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT mark_event_id_fk
FOREIGN KEY (mark_event_id)
REFERENCES mark_event (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_mark_event
for i in range(len(mark_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_mark_event
(
trademark_id,
mark_event_id,
mark_event_date
)
SELECT trademark.id, mark_event.id, %s
FROM trademark
CROSS JOIN mark_event
WHERE serial_number = %s
AND mark_event_category LIKE %s
AND mark_event_code LIKE %s
AND mark_event_description_text LIKE %s
AND mark_event_entry_number = %s
AND mark_event_additional_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
mark_event_id,
mark_event_date
)
FROM trademark_mark_event
WHERE trademark_id = trademark.id
AND mark_event_id = mark_event.id
AND mark_event_date = %s
)''',
(
mark_data['MarkEventDate'][i],
data['ApplicationNumberText'],
mark_data['MarkEventCategory'][i],
mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i],
mark_data['MarkEventDate'][i]
)
)
# create trademark_gs_bag table
# connects trademark to gs_bag
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_gs_bag
(
trademark_id INTEGER,
gs_bag_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT gs_bag_id_fk
FOREIGN KEY (gs_bag_id)
REFERENCES gs_bag (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_gs_bag
for i in range(len(gs_bag_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_gs_bag
(
trademark_id,
gs_bag_id
)
SELECT trademark.id, gs_bag.id
FROM trademark
CROSS JOIN gs_bag
WHERE serial_number = %s
AND classification_kind_code LIKE %s
AND (gs_bag.class_number = %s OR national_class_number = %s)
AND NOT EXISTS
(
SELECT
(
trademark_id,
gs_bag_id
)
FROM trademark_gs_bag
WHERE trademark_id = trademark.id
AND gs_bag_id = gs_bag.id
)''',
(
data['ApplicationNumberText'],
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i]
)
)
# create trademark_applicant table
# connects trademark to applicant
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_applicant
(
trademark_id INTEGER,
applicant_id INTEGER,
applicant_role TEXT,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT applicant_id_fk
FOREIGN KEY (applicant_id)
REFERENCES applicant (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_applicant
for i in range(len(applicant_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_applicant
(
trademark_id,
applicant_id,
applicant_role
)
SELECT trademark.id, applicant.id, %s
FROM trademark
CROSS JOIN applicant
WHERE serial_number = %s
AND incorporation_country_code LIKE %s
AND incorporation_state LIKE %s
AND organization_standard_name LIKE %s
AND entity_name LIKE %s
AND postal_code LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
applicant_id,
applicant_role
)
FROM trademark_applicant
WHERE trademark_id = trademark.id
AND applicant_id = applicant.id
AND applicant_role = %s
)''',
(
applicant_data['CommentText'][i],
data['ApplicationNumberText'],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i],
applicant_data['PostalCode'][i],
applicant_data['CommentText'][i]
)
)
# create trademark_national_trademark table
# connects trademark to nationa_trademark
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_national_trademark
(
trademark_id INTEGER,
national_trademark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT national_trademark_id_fk
FOREIGN KEY (national_trademark_id)
REFERENCES national_trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_national_trademark
cur.execute(
'''INSERT INTO trademark_national_trademark
(
trademark_id,
national_trademark_id
)
SELECT trademark.id, national_trademark.id
FROM trademark
CROSS JOIN national_trademark
WHERE serial_number = %s
AND register_category LIKE %s
AND amended_principal_register_indicator = %s
AND amended_supplemental_register_indicator = %s
AND mark_current_status_external_description_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
national_trademark_id
)
FROM trademark_national_trademark
WHERE trademark_id = trademark.id
AND national_trademark_id = national_trademark.id
)''',
(
data['ApplicationNumberText'],
nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText']
)
)
conn.commit() # validate all changes and commit them to postgres
conn.close()