India Export Database

Contents

13.12. India Export Database#

import pandas as pd
import sqlite3

df = pd.read_csv('india_export.csv')
display(df)

	HSCode	Commodity	value	country	year
0	2	MEAT AND EDIBLE MEAT OFFAL.	0.18	AFGHANISTAN TIS	2018
1	3	FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT...	0.00	AFGHANISTAN TIS	2018
2	4	DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI...	12.48	AFGHANISTAN TIS	2018
3	6	LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ...	0.00	AFGHANISTAN TIS	2018
4	7	EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.	1.89	AFGHANISTAN TIS	2018
...	...	...	...	...	...
137018	95	TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A...	0.03	ZIMBABWE	2010
137019	96	MISCELLANEOUS MANUFACTURED ARTICLES.	0.45	ZIMBABWE	2010
137020	97	WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.	0.00	ZIMBABWE	2010
137021	98	PROJECT GOODS; SOME SPECIAL USES.	0.00	ZIMBABWE	2010
137022	99	MISCELLANEOUS GOODS.	0.07	ZIMBABWE	2010

137023 rows × 5 columns

conn = sqlite3.connect('india_export.db')
cur = conn.cursor()
cur.execute('''DROP TABLE IF EXISTS export''')
df.to_sql('export', conn, if_exists='replace', index=False) # - writes the pd.df to SQLIte DB
conn.commit()

pd.read_sql('select * from export', conn)

	HSCode	Commodity	value	country	year
0	2	MEAT AND EDIBLE MEAT OFFAL.	0.18	AFGHANISTAN TIS	2018
1	3	FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT...	0.00	AFGHANISTAN TIS	2018
2	4	DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI...	12.48	AFGHANISTAN TIS	2018
3	6	LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ...	0.00	AFGHANISTAN TIS	2018
4	7	EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.	1.89	AFGHANISTAN TIS	2018
...	...	...	...	...	...
137018	95	TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A...	0.03	ZIMBABWE	2010
137019	96	MISCELLANEOUS MANUFACTURED ARTICLES.	0.45	ZIMBABWE	2010
137020	97	WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.	0.00	ZIMBABWE	2010
137021	98	PROJECT GOODS; SOME SPECIAL USES.	0.00	ZIMBABWE	2010
137022	99	MISCELLANEOUS GOODS.	0.07	ZIMBABWE	2010

137023 rows × 5 columns

conn.close()

13.13. Steps to normalizing India export database#

Inspect table and determine how many tables into convert the database into
- Four tables:
  - year
  - country
  - commodity
  - a table that joins country, year, commmodity, and value
Steps to create year table
- list of unique years
- create statement
- insert function

import pandas as pd
import sqlite3
from sqlite3 import Error

def create_connection(db_file, delete_db=False):
    import os
    if delete_db and os.path.exists(db_file):
        os.remove(db_file)

    conn = None
    try:
        conn = sqlite3.connect(db_file)
        conn.execute("PRAGMA foreign_keys = 1")
    except Error as e:
        print(e)

    return conn


def create_table(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)
        
def execute_sql_statement(sql_statement, conn):
    cur = conn.cursor()
    cur.execute(sql_statement)

    rows = cur.fetchall()

    return rows

conn = create_connection('india_export.db')

# Get list of unique years

sql_statement = "SELECT DISTINCT year from export ORDER BY year"

years = execute_sql_statement(sql_statement, conn)
years = list(map(lambda row: int(row[0]), years))
print(years)

[2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018]

create_table_sql = """CREATE TABLE [Year] (
    [YEAR] INTEGER NOT NULL PRIMARY KEY
);
"""
# conn_norm.close()

conn_norm = create_connection('india_export_norm.db', True)
create_table(conn_norm, create_table_sql)
sql_statement = "SELECT * FROM Year"
df = pd.read_sql_query(sql_statement, conn_norm)
display(df)
conn_norm.close()

	YEAR

conn_norm = create_connection('india_export_norm.db')
sql_statement = "SELECT * FROM Year"
df = pd.read_sql_query(sql_statement, conn_norm)
display(df)

	YEAR

(2022,)

(2022,)

def insert_year(conn, values):
    sql = ''' INSERT INTO YEAR(YEAR)
              VALUES(?) '''
    cur = conn.cursor()
    cur.execute(sql, values)
    return cur.lastrowid

with conn_norm:
    for year in years:
        insert_year(conn_norm, (year, ))

sql_statement = "SELECT * FROM Year"
df = pd.read_sql_query(sql_statement, conn_norm)
display(df)

	YEAR
0	2010
1	2011
2	2012
3	2013
4	2014
5	2015
6	2016
7	2017
8	2018

conn_norm.close()

conn_norm = create_connection('india_export_norm.db')
sql_statement = "SELECT * FROM Year"
df = pd.read_sql_query(sql_statement, conn_norm)
display(df)

	YEAR
0	2010
1	2011
2	2012
3	2013
4	2014
5	2015
6	2016
7	2017
8	2018

# Get list of unique countries

sql_statement = "SELECT DISTINCT country from export ORDER BY country"

countries = execute_sql_statement(sql_statement, conn)
countries = list(map(lambda row: row[0], countries))
print(countries)

['AFGHANISTAN TIS', 'ALBANIA', 'ALGERIA', 'AMERI SAMOA', 'ANDORRA', 'ANGOLA', 'ANGUILLA', 'ANTARTICA', 'ANTIGUA', 'ARGENTINA', 'ARMENIA', 'ARUBA', 'AUSTRALIA', 'AUSTRIA', 'AZERBAIJAN', 'BAHAMAS', 'BAHARAIN IS', 'BANGLADESH PR', 'BARBADOS', 'BELARUS', 'BELGIUM', 'BELIZE', 'BENIN', 'BERMUDA', 'BHUTAN', 'BOLIVIA', 'BOSNIA-HRZGOVIN', 'BOTSWANA', 'BR VIRGN IS', 'BRAZIL', 'BRUNEI', 'BULGARIA', 'BURKINA FASO', 'BURUNDI', 'C AFRI REP', 'CAMBODIA', 'CAMEROON', 'CANADA', 'CANARY IS', 'CAPE VERDE IS', 'CAYMAN IS', 'CHAD', 'CHANNEL IS', 'CHILE', 'CHINA P RP', 'CHRISTMAS IS.', 'COCOS IS', 'COLOMBIA', 'COMOROS', 'CONGO D. REP.', 'CONGO P REP', 'COOK IS', 'COSTA RICA', "COTE D' IVOIRE", 'CROATIA', 'CUBA', 'CURACAO', 'CYPRUS', 'CZECH REPUBLIC', 'DENMARK', 'DJIBOUTI', 'DOMINIC REP', 'DOMINICA', 'ECUADOR', 'EGYPT A RP', 'EL SALVADOR', 'EQUTL GUINEA', 'ERITREA', 'ESTONIA', 'ETHIOPIA', 'FALKLAND IS', 'FAROE IS.', 'FIJI IS', 'FINLAND', 'FR GUIANA', 'FR POLYNESIA', 'FR S ANT TR', 'FRANCE', 'GABON', 'GAMBIA', 'GEORGIA', 'GERMANY', 'GHANA', 'GIBRALTAR', 'GREECE', 'GREENLAND', 'GRENADA', 'GUADELOUPE', 'GUAM', 'GUATEMALA', 'GUERNSEY', 'GUINEA', 'GUINEA BISSAU', 'GUYANA', 'HAITI', 'HEARD MACDONALD', 'HONDURAS', 'HONG KONG', 'HUNGARY', 'ICELAND', 'INDONESIA', 'INSTALLATIONS IN INTERNATIONAL WATERS   ', 'IRAN', 'IRAQ', 'IRELAND', 'ISRAEL', 'ITALY', 'JAMAICA', 'JAPAN', 'JERSEY         ', 'JORDAN', 'KAZAKHSTAN', 'KENYA', 'KIRIBATI REP', 'KOREA DP RP', 'KOREA RP', 'KUWAIT', 'KYRGHYZSTAN', 'LAO PD RP', 'LATVIA', 'LEBANON', 'LESOTHO', 'LIBERIA', 'LIBYA', 'LIECHTENSTEIN', 'LITHUANIA', 'LUXEMBOURG', 'MACAO', 'MACEDONIA', 'MADAGASCAR', 'MALAWI', 'MALAYSIA', 'MALDIVES', 'MALI', 'MALTA', 'MARSHALL ISLAND', 'MARTINIQUE', 'MAURITANIA', 'MAURITIUS', 'MAYOTTE', 'MEXICO', 'MICRONESIA', 'MOLDOVA', 'MONACO', 'MONGOLIA', 'MONTENEGRO', 'MONTSERRAT', 'MOROCCO', 'MOZAMBIQUE', 'MYANMAR', 'N. MARIANA IS.', 'NAMIBIA', 'NAURU RP', 'NEPAL', 'NETHERLAND', 'NETHERLANDANTIL', 'NEUTRAL ZONE', 'NEW CALEDONIA', 'NEW ZEALAND', 'NICARAGUA', 'NIGER', 'NIGERIA', 'NIUE IS', 'NORFOLK IS', 'NORWAY', 'OMAN', 'PACIFIC IS', 'PAKISTAN IR', 'PALAU', 'PANAMA C Z', 'PANAMA REPUBLIC', 'PAPUA N GNA', 'PARAGUAY', 'PERU', 'PHILIPPINES', 'PITCAIRN IS.', 'POLAND', 'PORTUGAL', 'PUERTO RICO', 'QATAR', 'REUNION', 'ROMANIA', 'RUSSIA', 'RWANDA', 'SAHARWI A.DM RP', 'SAMOA', 'SAN MARINO', 'SAO TOME', 'SAUDI ARAB', 'SENEGAL', 'SERBIA', 'SEYCHELLES', 'SIERRA LEONE', 'SINGAPORE', 'SINT MAARTEN (DUTCH PART)', 'SLOVAK REP', 'SLOVENIA', 'SOLOMON IS', 'SOMALIA', 'SOUTH AFRICA', 'SOUTH SUDAN ', 'SPAIN', 'SRI LANKA DSR', 'ST HELENA', 'ST KITT N A', 'ST LUCIA', 'ST PIERRE', 'ST VINCENT', 'STATE OF PALEST', 'SUDAN', 'SURINAME', 'SWAZILAND', 'SWEDEN', 'SWITZERLAND', 'SYRIA', 'TAIWAN', 'TAJIKISTAN', 'TANZANIA REP', 'THAILAND', 'TIMOR LESTE', 'TOGO', 'TOKELAU IS', 'TONGA', 'TRINIDAD', 'TUNISIA', 'TURKEY', 'TURKMENISTAN', 'TURKS C IS', 'TUVALU', 'U ARAB EMTS', 'U K', 'U S A', 'UGANDA', 'UKRAINE', 'UNION OF SERBIA & MONTENEGRO', 'UNSPECIFIED', 'URUGUAY', 'US MINOR OUTLYING ISLANDS               ', 'UZBEKISTAN', 'VANUATU REP', 'VATICAN CITY', 'VENEZUELA', 'VIETNAM SOC REP', 'VIRGIN IS US', 'WALLIS F IS', 'YEMEN REPUBLC', 'ZAMBIA', 'ZIMBABWE']

def insert_country(conn, values):
    sql = ''' INSERT INTO COUNTRY(COUNTRY)
              VALUES(?) '''
    cur = conn.cursor()
    cur.execute(sql, values)
    return cur.lastrowid

create_table_sql = """CREATE TABLE IF NOT EXISTS [Country] (
    [Country] TEXT NOT NULL PRIMARY KEY
);
"""
conn_norm = create_connection('india_export_norm.db')

create_table(conn_norm, create_table_sql)

with conn_norm:
    for country in countries:
        insert_country(conn_norm, (country, ))
        
sql_statement = "SELECT * FROM country"
df = pd.read_sql_query(sql_statement, conn_norm)
display(df)

	Country
0	AFGHANISTAN TIS
1	ALBANIA
2	ALGERIA
3	AMERI SAMOA
4	ANDORRA
...	...
243	VIRGIN IS US
244	WALLIS F IS
245	YEMEN REPUBLC
246	ZAMBIA
247	ZIMBABWE

248 rows × 1 columns

sql_statement = "SELECT DISTINCT COMMODITY FROM export order by COMMODITY"
commodities = execute_sql_statement(sql_statement, conn)
print(commodities)
print(len(commodities))

[('AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.',), ('ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GLUES; ENZYMES.',), ('ALUMINIUM AND ARTICLES THEREOF.',), ('ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CLEAVAGE PRODUCTS; PRE. EDIBLE FATS; ANIMAL OR VEGETABLE WAXEX.',), ('ARMS AND AMMUNITION; PARTS AND ACCESSORIES THEREOF.',), ('ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, KNITTED OR CORCHETED.',), ('ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, NOT KNITTED OR CROCHETED.',), ('ARTICLES OF IRON OR STEEL',), ('ARTICLES OF LEATHER,SADDLERY AND HARNESS;TRAVEL GOODS, HANDBAGS AND SIMILAR CONT.ARTICLES OF ANIMAL GUT(OTHR THN SILK-WRM)GUT.',), ('ARTICLES OF STONE, PLASTER, CEMENT, ASBESTOS, MICA OR SIMILAR MATERIALS.',), ('BEVERAGES, SPIRITS AND VINEGAR.',), ('CARPETS AND OTHER TEXTILE FLOOR COVERINGS.',), ('CERAMIC PRODUCTS.',), ('CEREALS.',), ('CLOCKS AND WATCHES AND PARTS THEREOF.',), ('COCOA AND COCOA PREPARATIONS.',), ('COFFEE, TEA, MATE AND SPICES.',), ('COPPER AND ARTICLES THEREOF.',), ('CORK AND ARTICLES OF CORK.',), ('COTTON.',), ("DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDIBLE PROD. OF ANIMAL ORIGIN, NOT ELSEWHERE SPEC. OR INCLUDED.",), ('EDIBLE FRUIT AND NUTS; PEEL OR CITRUS FRUIT OR MELONS.',), ('EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.',), ('ELECTRICAL MACHINERY AND EQUIPMENT AND PARTS THEREOF; SOUND RECORDERS AND REPRODUCERS, TELEVISION IMAGE AND SOUND RECORDERS AND REPRODUCERS,AND PARTS.',), ('ESSENTIAL OILS AND RESINOIDS; PERFUMERY, COSMETIC OR TOILET PREPARATIONS.',), ('EXPLOSIVES; PYROTECHNIC PRODUCTS; MATCHES; PYROPHORIC ALLOYS; CERTAIN COMBUSTIBLE PREPARATIONS.',), ('FERTILISERS.',), ('FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUATIC INVERTABRATES.',), ('FOOTWEAR, GAITERS AND THE LIKE; PARTS OF SUCH ARTICLES.',), ('FURNITURE; BEDDING, MATTRESSES, MATTRESS SUPPORTS, CUSHIONS AND SIMILAR STUFFED FURNISHING; LAMPS AND LIGHTING FITTINGS NOT ELSEWHERE SPECIFIED OR INC',), ('FURSKINS AND ARTIFICIAL FUR, MANUFACTURES THEREOF.',), ('GLASS AND GLASSWARE.',), ('HEADGEAR AND PARTS THEREOF.',), ('IMPREGNATED, COATED, COVERED OR LAMINATED TEXTILE FABRICS; TEXTILE ARTICLES OF A KIND SUITABLE FOR INDUSTRIAL USE.',), ('INORGANIC CHEMICALS; ORGANIC OR INORGANIC COMPOUNDS OF PRECIOUS METALS, OF RARE-EARTH METALS, OR RADI. ELEM. OR OF ISOTOPES.',), ('IRON AND STEEL',), ('KNITTED OR CROCHETED FABRICS.',), ('LAC; GUMS, RESINS AND OTHER VEGETABLE SAPS AND EXTRACTS.',), ('LEAD AND ARTICLES THEREOF.',), ('LIVE ANIMALS.',), ('LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND THE LIKE; CUT FLOWERS AND ORNAMENTAL FOLIAGE.',), ('MAN-MADE FILAMENTS.',), ('MAN-MADE STAPLE FIBRES.',), ('MANUFACTURES OF STRAW, OF ESPARTO OR OF OTHER PLAITING MATERIALS; BASKETWARE AND WICKERWORK.',), ('MEAT AND EDIBLE MEAT OFFAL.',), ('MINERAL FUELS, MINERAL OILS AND PRODUCTS OF THEIR DISTILLATION; BITUMINOUS SUBSTANCES; MINERAL WAXES.',), ('MISCELLANEOUS ARTICLES OF BASE METAL.',), ('MISCELLANEOUS CHEMICAL PRODUCTS.',), ('MISCELLANEOUS EDIBLE PREPARATIONS.',), ('MISCELLANEOUS GOODS.',), ('MISCELLANEOUS MANUFACTURED ARTICLES.',), ('MUSICAL INSTRUMENTS; PARTS AND ACCESSORIES OF SUCH ARTICLES.',), ('NATURAL OR CULTURED PEARLS,PRECIOUS OR SEMIPRECIOUS STONES,PRE.METALS,CLAD WITH PRE.METAL AND ARTCLS THEREOF;IMIT.JEWLRY;COIN.',), ('NICKEL AND ARTICLES THEREOF.',), ('NUCLEAR REACTORS, BOILERS, MACHINERY AND MECHANICAL APPLIANCES; PARTS THEREOF.',), ('OIL SEEDS AND OLEA. FRUITS; MISC. GRAINS, SEEDS AND FRUIT; INDUSTRIAL OR MEDICINAL PLANTS; STRAW AND FODDER.',), ('OPTICAL, PHOTOGRAPHIC CINEMATOGRAPHIC MEASURING, CHECKING PRECISION, MEDICAL OR SURGICAL INST. AND APPARATUS PARTS AND ACCESSORIES THEREOF;',), ('ORES, SLAG AND ASH.',), ('ORGANIC CHEMICALS',), ('OTHER BASE METALS; CERMETS; ARTICLES THEREOF.',), ('OTHER MADE UP TEXTILE ARTICLES; SETS; WORN CLOTHING AND WORN TEXTILE ARTICLES; RAGS',), ('OTHER VEGETABLE TEXTILE FIBRES; PAPER YARN AND WOVEN FABRICS OF PAPER YARN.',), ('PAPER AND PAPERBOARD; ARTICLES OF PAPER PULP, OF PAPER OR OF PAPERBOARD.',), ('PHARMACEUTICAL PRODUCTS',), ('PHOTOGRAPHIC OR CINEMATOGRAPHIC GOODS.',), ('PLASTIC AND ARTICLES THEREOF.',), ('PREPARATIONS OF CEREALS, FLOUR, STARCH OR MILK; PASTRYCOOKS PRODUCTS.',), ('PREPARATIONS OF MEAT, OF FISH OR OF CRUSTACEANS, MOLLUSCS OR OTHER AQUATIC INVERTEBRATES',), ('PREPARATIONS OF VEGETABLES, FRUIT, NUTS OR OTHER PARTS OF PLANTS.',), ('PREPARED FEATHERS AND DOWN AND ARTICLES MADE OF FEATHERS OR OF DOWN; ARTIFICIAL FLOWERS; ARTICLES OF HUMAN HAIR.',), ('PRINTED BOOKDS, NEWSPAPERS, PICTURES AND OTHER PRODUCTS OF THE PRINTING INDUSTRY; MANUSCRIPTS, TYPESCRIPTS AND PLANS.',), ('PRODUCTS OF ANIMAL ORIGIN, NOT ELSEWHERE SPECIFIED OR INCLUDED.',), ('PRODUCTS OF THE MILLING INDUSTRY; MALT; STARCHES; INULIN; WHEAT GLUTEN.',), ('PROJECT GOODS; SOME SPECIAL USES.',), ('PULP OF WOOD OR OF OTHER FIBROUS CELLULOSIC MATERIAL; WASTE AND SCRAP OF PAPER OR PAPERBOARD.',), ('RAILWAY OR TRAMWAY LOCOMOTIVES, ROLLING-STOCK AND PARTS THEREOF; RAILWAY OR TRAMWAY TRACK FIXTURES AND FITTINGS AND PARTS THEREOF; MECHANICAL',), ('RAW HIDES AND SKINS (OTHER THAN FURSKINS) AND LEATHER',), ('RESIDUES AND WASTE FROM THE FOOD INDUSTRIES; PREPARED ANIMAL FODER.',), ('RUBBER AND ARTICLES THEREOF.',), ('SALT; SULPHUR; EARTHS AND STONE; PLASTERING MATERIALS, LIME AND CEMENT.',), ('SHIPS, BOATS AND FLOATING STRUCTURES.',), ('SILK',), ('SOAP, ORGANIC SURFACE-ACTIVE AGENTS, WASHING PREPARATIONS, LUBRICATING PREPARATIONS, ARTIFICIAL WAXES, PREPARED WAXES, POLISHING OR SCOURING PREP.',), ('SPECIAL WOVEN FABRICS; TUFTED TEXTILE FABRICS; LACE; TAPESTRIES; TRIMMINGS; EMBROIDERY.',), ('SUGARS AND SUGAR CONFECTIONERY.',), ('TANNING OR DYEING EXTRACTS; TANNINS AND THEIR DERI. DYES, PIGMENTS AND OTHER COLOURING MATTER; PAINTS AND VER; PUTTY AND OTHER MASTICS; INKS.',), ('TIN AND ARTICLES THEREOF.',), ('TOBACCO AND MANUFACTURED TOBACCO SUBSTITUTES.',), ('TOOLS IMPLEMENTS, CUTLERY, SPOONS AND FORKS, OF BASE METAL; PARTS THEREOF OF BASE METAL.',), ('TOYS, GAMES AND SPORTS REQUISITES; PARTS AND ACCESSORIES THEREOF.',), ('UMBRELLAS, SUN UMBRELLAS, WALKING-STICKS, SEAT-STICKS, WHIPS,RIDING-CROPS AND PARTS THEREOF.',), ('VEGETABLE PLAITING MATERIALS; VEGETABLE PRODUCTS NOT ELSEWHERE SPECIFIED OR INCLUDED.',), ('VEHICLES OTHER THAN RAILWAY OR TRAMWAY ROLLING STOCK, AND PARTS AND ACCESSORIES THEREOF.',), ('WADDING, FELT AND NONWOVENS; SPACIAL YARNS; TWINE, CORDAGE, ROPES AND CABLES AND ARTICLES THEREOF.',), ('WOOD AND ARTICLES OF WOOD; WOOD CHARCOAL.',), ('WOOL, FINE OR COARSE ANIMAL HAIR, HORSEHAIR YARN AND WOVEN FABRIC.',), ("WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.",), ('ZINC AND ARTICLES THEREOF.',)]
98

def insert_commodity(conn, values):
    sql = ''' INSERT INTO COMMODITY (COMMODITY)
              VALUES(?) '''
    cur = conn.cursor()
    cur.execute(sql, values)
    return cur.lastrowid

create_table_sql = """CREATE TABLE IF NOT EXISTS [COMMODITY] (
    [HSCODE] INTEGER NOT NULL PRIMARY KEY, 
    [COMMODITY] TEXT NOT NULL
);
"""
create_table(conn_norm, create_table_sql)

with conn_norm:
    for commodity in commodities:
        insert_commodity(conn_norm, commodity)
        
sql_statement = "SELECT * FROM Commodity"
df = pd.read_sql_query(sql_statement, conn_norm)
display(df)

	HSCODE	COMMODITY
0	1	AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.
1	2	ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GL...
2	3	ALUMINIUM AND ARTICLES THEREOF.
3	4	ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CL...
4	5	ARMS AND AMMUNITION; PARTS AND ACCESSORIES THE...
...	...	...
93	94	WADDING, FELT AND NONWOVENS; SPACIAL YARNS; TW...
94	95	WOOD AND ARTICLES OF WOOD; WOOD CHARCOAL.
95	96	WOOL, FINE OR COARSE ANIMAL HAIR, HORSEHAIR YA...
96	97	WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.
97	98	ZINC AND ARTICLES THEREOF.

98 rows × 2 columns

# Table 4 which is going link all the tables with values

def insert_value(conn, values):
    sql = ''' INSERT INTO Export (HSCODE, COUNTRY, YEAR, VALUE)
              VALUES(?, ?, ?, ?) '''
    cur = conn.cursor()
    cur.execute(sql, values)
    return cur.lastrowid

create_table_sql = """CREATE TABLE IF NOT EXISTS [Export] (
    [VALUEID] INTEGER NOT NULL PRIMARY KEY,
    [HSCODE] INTEGER NOT NULL, 
    [COUNTRY] TEXT NOT NULL,
    [YEAR] INTEGER NOT NULL,
    [VALUE] REAL,
    FOREIGN KEY(COUNTRY) REFERENCES Country(COUNTRY),
    FOREIGN KEY(YEAR) REFERENCES YEAR(YEAR),
    FOREIGN KEY(HSCODE) REFERENCES Commodity(HSCODE)
);
"""
create_table(conn_norm, create_table_sql)

sql_statement = 'SELECT Commodity, country, year, value from export'
values = execute_sql_statement(sql_statement, conn)
print(values[:10])

[('MEAT AND EDIBLE MEAT OFFAL.', 'AFGHANISTAN TIS', 2018, 0.18), ('FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUATIC INVERTABRATES.', 'AFGHANISTAN TIS', 2018, 0.0), ("DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDIBLE PROD. OF ANIMAL ORIGIN, NOT ELSEWHERE SPEC. OR INCLUDED.", 'AFGHANISTAN TIS', 2018, 12.48), ('LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND THE LIKE; CUT FLOWERS AND ORNAMENTAL FOLIAGE.', 'AFGHANISTAN TIS', 2018, 0.0), ('EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.', 'AFGHANISTAN TIS', 2018, 1.89), ('EDIBLE FRUIT AND NUTS; PEEL OR CITRUS FRUIT OR MELONS.', 'AFGHANISTAN TIS', 2018, 25.01), ('COFFEE, TEA, MATE AND SPICES.', 'AFGHANISTAN TIS', 2018, 13.75), ('CEREALS.', 'AFGHANISTAN TIS', 2018, 0.75), ('PRODUCTS OF THE MILLING INDUSTRY; MALT; STARCHES; INULIN; WHEAT GLUTEN.', 'AFGHANISTAN TIS', 2018, 0.01), ('OIL SEEDS AND OLEA. FRUITS; MISC. GRAINS, SEEDS AND FRUIT; INDUSTRIAL OR MEDICINAL PLANTS; STRAW AND FODDER.', 'AFGHANISTAN TIS', 2018, 10.02)]

sql_statement = 'SELECT HSCODE, COMMODITY from COMMODITY'
values = execute_sql_statement(sql_statement, conn_norm)
print(values[:10])

[(1, 'AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.'), (2, 'ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GLUES; ENZYMES.'), (3, 'ALUMINIUM AND ARTICLES THEREOF.'), (4, 'ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CLEAVAGE PRODUCTS; PRE. EDIBLE FATS; ANIMAL OR VEGETABLE WAXEX.'), (5, 'ARMS AND AMMUNITION; PARTS AND ACCESSORIES THEREOF.'), (6, 'ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, KNITTED OR CORCHETED.'), (7, 'ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, NOT KNITTED OR CROCHETED.'), (8, 'ARTICLES OF IRON OR STEEL'), (9, 'ARTICLES OF LEATHER,SADDLERY AND HARNESS;TRAVEL GOODS, HANDBAGS AND SIMILAR CONT.ARTICLES OF ANIMAL GUT(OTHR THN SILK-WRM)GUT.'), (10, 'ARTICLES OF STONE, PLASTER, CEMENT, ASBESTOS, MICA OR SIMILAR MATERIALS.')]

commodity_lookup = {}
for hscode, commodity in values:
    commodity_lookup[commodity] = hscode
    
print(commodity_lookup)

{'AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.': 1, 'ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GLUES; ENZYMES.': 2, 'ALUMINIUM AND ARTICLES THEREOF.': 3, 'ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CLEAVAGE PRODUCTS; PRE. EDIBLE FATS; ANIMAL OR VEGETABLE WAXEX.': 4, 'ARMS AND AMMUNITION; PARTS AND ACCESSORIES THEREOF.': 5, 'ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, KNITTED OR CORCHETED.': 6, 'ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, NOT KNITTED OR CROCHETED.': 7, 'ARTICLES OF IRON OR STEEL': 8, 'ARTICLES OF LEATHER,SADDLERY AND HARNESS;TRAVEL GOODS, HANDBAGS AND SIMILAR CONT.ARTICLES OF ANIMAL GUT(OTHR THN SILK-WRM)GUT.': 9, 'ARTICLES OF STONE, PLASTER, CEMENT, ASBESTOS, MICA OR SIMILAR MATERIALS.': 10, 'BEVERAGES, SPIRITS AND VINEGAR.': 11, 'CARPETS AND OTHER TEXTILE FLOOR COVERINGS.': 12, 'CERAMIC PRODUCTS.': 13, 'CEREALS.': 14, 'CLOCKS AND WATCHES AND PARTS THEREOF.': 15, 'COCOA AND COCOA PREPARATIONS.': 16, 'COFFEE, TEA, MATE AND SPICES.': 17, 'COPPER AND ARTICLES THEREOF.': 18, 'CORK AND ARTICLES OF CORK.': 19, 'COTTON.': 20, "DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDIBLE PROD. OF ANIMAL ORIGIN, NOT ELSEWHERE SPEC. OR INCLUDED.": 21, 'EDIBLE FRUIT AND NUTS; PEEL OR CITRUS FRUIT OR MELONS.': 22, 'EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.': 23, 'ELECTRICAL MACHINERY AND EQUIPMENT AND PARTS THEREOF; SOUND RECORDERS AND REPRODUCERS, TELEVISION IMAGE AND SOUND RECORDERS AND REPRODUCERS,AND PARTS.': 24, 'ESSENTIAL OILS AND RESINOIDS; PERFUMERY, COSMETIC OR TOILET PREPARATIONS.': 25, 'EXPLOSIVES; PYROTECHNIC PRODUCTS; MATCHES; PYROPHORIC ALLOYS; CERTAIN COMBUSTIBLE PREPARATIONS.': 26, 'FERTILISERS.': 27, 'FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUATIC INVERTABRATES.': 28, 'FOOTWEAR, GAITERS AND THE LIKE; PARTS OF SUCH ARTICLES.': 29, 'FURNITURE; BEDDING, MATTRESSES, MATTRESS SUPPORTS, CUSHIONS AND SIMILAR STUFFED FURNISHING; LAMPS AND LIGHTING FITTINGS NOT ELSEWHERE SPECIFIED OR INC': 30, 'FURSKINS AND ARTIFICIAL FUR, MANUFACTURES THEREOF.': 31, 'GLASS AND GLASSWARE.': 32, 'HEADGEAR AND PARTS THEREOF.': 33, 'IMPREGNATED, COATED, COVERED OR LAMINATED TEXTILE FABRICS; TEXTILE ARTICLES OF A KIND SUITABLE FOR INDUSTRIAL USE.': 34, 'INORGANIC CHEMICALS; ORGANIC OR INORGANIC COMPOUNDS OF PRECIOUS METALS, OF RARE-EARTH METALS, OR RADI. ELEM. OR OF ISOTOPES.': 35, 'IRON AND STEEL': 36, 'KNITTED OR CROCHETED FABRICS.': 37, 'LAC; GUMS, RESINS AND OTHER VEGETABLE SAPS AND EXTRACTS.': 38, 'LEAD AND ARTICLES THEREOF.': 39, 'LIVE ANIMALS.': 40, 'LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND THE LIKE; CUT FLOWERS AND ORNAMENTAL FOLIAGE.': 41, 'MAN-MADE FILAMENTS.': 42, 'MAN-MADE STAPLE FIBRES.': 43, 'MANUFACTURES OF STRAW, OF ESPARTO OR OF OTHER PLAITING MATERIALS; BASKETWARE AND WICKERWORK.': 44, 'MEAT AND EDIBLE MEAT OFFAL.': 45, 'MINERAL FUELS, MINERAL OILS AND PRODUCTS OF THEIR DISTILLATION; BITUMINOUS SUBSTANCES; MINERAL WAXES.': 46, 'MISCELLANEOUS ARTICLES OF BASE METAL.': 47, 'MISCELLANEOUS CHEMICAL PRODUCTS.': 48, 'MISCELLANEOUS EDIBLE PREPARATIONS.': 49, 'MISCELLANEOUS GOODS.': 50, 'MISCELLANEOUS MANUFACTURED ARTICLES.': 51, 'MUSICAL INSTRUMENTS; PARTS AND ACCESSORIES OF SUCH ARTICLES.': 52, 'NATURAL OR CULTURED PEARLS,PRECIOUS OR SEMIPRECIOUS STONES,PRE.METALS,CLAD WITH PRE.METAL AND ARTCLS THEREOF;IMIT.JEWLRY;COIN.': 53, 'NICKEL AND ARTICLES THEREOF.': 54, 'NUCLEAR REACTORS, BOILERS, MACHINERY AND MECHANICAL APPLIANCES; PARTS THEREOF.': 55, 'OIL SEEDS AND OLEA. FRUITS; MISC. GRAINS, SEEDS AND FRUIT; INDUSTRIAL OR MEDICINAL PLANTS; STRAW AND FODDER.': 56, 'OPTICAL, PHOTOGRAPHIC CINEMATOGRAPHIC MEASURING, CHECKING PRECISION, MEDICAL OR SURGICAL INST. AND APPARATUS PARTS AND ACCESSORIES THEREOF;': 57, 'ORES, SLAG AND ASH.': 58, 'ORGANIC CHEMICALS': 59, 'OTHER BASE METALS; CERMETS; ARTICLES THEREOF.': 60, 'OTHER MADE UP TEXTILE ARTICLES; SETS; WORN CLOTHING AND WORN TEXTILE ARTICLES; RAGS': 61, 'OTHER VEGETABLE TEXTILE FIBRES; PAPER YARN AND WOVEN FABRICS OF PAPER YARN.': 62, 'PAPER AND PAPERBOARD; ARTICLES OF PAPER PULP, OF PAPER OR OF PAPERBOARD.': 63, 'PHARMACEUTICAL PRODUCTS': 64, 'PHOTOGRAPHIC OR CINEMATOGRAPHIC GOODS.': 65, 'PLASTIC AND ARTICLES THEREOF.': 66, 'PREPARATIONS OF CEREALS, FLOUR, STARCH OR MILK; PASTRYCOOKS PRODUCTS.': 67, 'PREPARATIONS OF MEAT, OF FISH OR OF CRUSTACEANS, MOLLUSCS OR OTHER AQUATIC INVERTEBRATES': 68, 'PREPARATIONS OF VEGETABLES, FRUIT, NUTS OR OTHER PARTS OF PLANTS.': 69, 'PREPARED FEATHERS AND DOWN AND ARTICLES MADE OF FEATHERS OR OF DOWN; ARTIFICIAL FLOWERS; ARTICLES OF HUMAN HAIR.': 70, 'PRINTED BOOKDS, NEWSPAPERS, PICTURES AND OTHER PRODUCTS OF THE PRINTING INDUSTRY; MANUSCRIPTS, TYPESCRIPTS AND PLANS.': 71, 'PRODUCTS OF ANIMAL ORIGIN, NOT ELSEWHERE SPECIFIED OR INCLUDED.': 72, 'PRODUCTS OF THE MILLING INDUSTRY; MALT; STARCHES; INULIN; WHEAT GLUTEN.': 73, 'PROJECT GOODS; SOME SPECIAL USES.': 74, 'PULP OF WOOD OR OF OTHER FIBROUS CELLULOSIC MATERIAL; WASTE AND SCRAP OF PAPER OR PAPERBOARD.': 75, 'RAILWAY OR TRAMWAY LOCOMOTIVES, ROLLING-STOCK AND PARTS THEREOF; RAILWAY OR TRAMWAY TRACK FIXTURES AND FITTINGS AND PARTS THEREOF; MECHANICAL': 76, 'RAW HIDES AND SKINS (OTHER THAN FURSKINS) AND LEATHER': 77, 'RESIDUES AND WASTE FROM THE FOOD INDUSTRIES; PREPARED ANIMAL FODER.': 78, 'RUBBER AND ARTICLES THEREOF.': 79, 'SALT; SULPHUR; EARTHS AND STONE; PLASTERING MATERIALS, LIME AND CEMENT.': 80, 'SHIPS, BOATS AND FLOATING STRUCTURES.': 81, 'SILK': 82, 'SOAP, ORGANIC SURFACE-ACTIVE AGENTS, WASHING PREPARATIONS, LUBRICATING PREPARATIONS, ARTIFICIAL WAXES, PREPARED WAXES, POLISHING OR SCOURING PREP.': 83, 'SPECIAL WOVEN FABRICS; TUFTED TEXTILE FABRICS; LACE; TAPESTRIES; TRIMMINGS; EMBROIDERY.': 84, 'SUGARS AND SUGAR CONFECTIONERY.': 85, 'TANNING OR DYEING EXTRACTS; TANNINS AND THEIR DERI. DYES, PIGMENTS AND OTHER COLOURING MATTER; PAINTS AND VER; PUTTY AND OTHER MASTICS; INKS.': 86, 'TIN AND ARTICLES THEREOF.': 87, 'TOBACCO AND MANUFACTURED TOBACCO SUBSTITUTES.': 88, 'TOOLS IMPLEMENTS, CUTLERY, SPOONS AND FORKS, OF BASE METAL; PARTS THEREOF OF BASE METAL.': 89, 'TOYS, GAMES AND SPORTS REQUISITES; PARTS AND ACCESSORIES THEREOF.': 90, 'UMBRELLAS, SUN UMBRELLAS, WALKING-STICKS, SEAT-STICKS, WHIPS,RIDING-CROPS AND PARTS THEREOF.': 91, 'VEGETABLE PLAITING MATERIALS; VEGETABLE PRODUCTS NOT ELSEWHERE SPECIFIED OR INCLUDED.': 92, 'VEHICLES OTHER THAN RAILWAY OR TRAMWAY ROLLING STOCK, AND PARTS AND ACCESSORIES THEREOF.': 93, 'WADDING, FELT AND NONWOVENS; SPACIAL YARNS; TWINE, CORDAGE, ROPES AND CABLES AND ARTICLES THEREOF.': 94, 'WOOD AND ARTICLES OF WOOD; WOOD CHARCOAL.': 95, 'WOOL, FINE OR COARSE ANIMAL HAIR, HORSEHAIR YARN AND WOVEN FABRIC.': 96, "WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.": 97, 'ZINC AND ARTICLES THEREOF.': 98}

commodity_lookup['MEAT AND EDIBLE MEAT OFFAL.']

# create_table_sql = """CREATE TABLE IF NOT EXISTS [Export] (
#     [VALUEID] INTEGER NOT NULL PRIMARY KEY,
#     [HSCODE] INTEGER NOT NULL, 
#     [COUNTRY] TEXT NOT NULL,
#     [YEAR] INTEGER NOT NULL,
#     [VALUE] REAL,
#     FOREIGN KEY(COUNTRY) REFERENCES Country(COUNTRY),
#     FOREIGN KEY(YEAR) REFERENCES YEAR(YEAR),
#     FOREIGN KEY(HSCODE) REFERENCES Commodity(HSCODE)
# );
# """

sql_statement = 'SELECT Commodity, country, year, value from export'
values = execute_sql_statement(sql_statement, conn)
# with conn_norm:
count = 0
with conn_norm:
    for value in values:
        text = value[0]
        hscode = commodity_lookup[text]
        insert_tuple = (hscode, value[1], value[2], value[3])
        insert_value(conn_norm, insert_tuple)

sql_statement = """SELECT 
export.hscode,
Commodity.COMMODITY,
export.country,
export.year,
export.value
FROM Export
JOIN Commodity ON Commodity.hscode = export.hscode
"""
df = pd.read_sql_query(sql_statement, conn_norm)
display(df)

	HSCODE	COMMODITY	COUNTRY	YEAR	VALUE
0	45	MEAT AND EDIBLE MEAT OFFAL.	AFGHANISTAN TIS	2018	0.18
1	28	FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT...	AFGHANISTAN TIS	2018	0.00
2	21	DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI...	AFGHANISTAN TIS	2018	12.48
3	41	LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ...	AFGHANISTAN TIS	2018	0.00
4	23	EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.	AFGHANISTAN TIS	2018	1.89
...	...	...	...	...	...
137018	90	TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A...	ZIMBABWE	2010	0.03
137019	51	MISCELLANEOUS MANUFACTURED ARTICLES.	ZIMBABWE	2010	0.45
137020	97	WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.	ZIMBABWE	2010	0.00
137021	74	PROJECT GOODS; SOME SPECIAL USES.	ZIMBABWE	2010	0.00
137022	50	MISCELLANEOUS GOODS.	ZIMBABWE	2010	0.07

137023 rows × 5 columns

conn_norm.close()

import time
time.monotonic() 

1399.580931574

conn_norm = create_connection('india_export_norm.db')
cur = conn_norm.cursor()
cur.execute('DROP TABLE EXPORT')
create_table_sql = """CREATE TABLE IF NOT EXISTS [Export] (
    [VALUEID] INTEGER NOT NULL PRIMARY KEY,
    [HSCODE] INTEGER NOT NULL, 
    [COUNTRY] TEXT NOT NULL,
    [YEAR] INTEGER NOT NULL,
    [VALUE] REAL,
    FOREIGN KEY(COUNTRY) REFERENCES Country(COUNTRY),
    FOREIGN KEY(YEAR) REFERENCES YEAR(YEAR),
    FOREIGN KEY(HSCODE) REFERENCES Commodity(HSCODE)
);
"""
create_table(conn_norm, create_table_sql)

with conn_norm:
    start_time = time.monotonic() 
    for value in values:
        text = value[0]
        hscode = commodity_lookup[text]
        insert_tuple = (hscode, value[1], value[2], value[3])
        insert_value(conn_norm, insert_tuple)
end_time = time.monotonic()     


print(end_time-start_time)

0.44779851699991013

insert_tuples = []

for value in values:
    text = value[0]
    hscode = commodity_lookup[text]
    insert_tuple = (hscode, value[1], value[2], value[3])
    insert_tuples.append(insert_tuple)
print(insert_tuples[:100])

[(45, 'AFGHANISTAN TIS', 2018, 0.18), (28, 'AFGHANISTAN TIS', 2018, 0.0), (21, 'AFGHANISTAN TIS', 2018, 12.48), (41, 'AFGHANISTAN TIS', 2018, 0.0), (23, 'AFGHANISTAN TIS', 2018, 1.89), (22, 'AFGHANISTAN TIS', 2018, 25.01), (17, 'AFGHANISTAN TIS', 2018, 13.75), (14, 'AFGHANISTAN TIS', 2018, 0.75), (73, 'AFGHANISTAN TIS', 2018, 0.01), (56, 'AFGHANISTAN TIS', 2018, 10.02), (38, 'AFGHANISTAN TIS', 2018, 0.04), (92, 'AFGHANISTAN TIS', 2018, None), (4, 'AFGHANISTAN TIS', 2018, 0.37), (85, 'AFGHANISTAN TIS', 2018, 15.92), (16, 'AFGHANISTAN TIS', 2018, 0.0), (67, 'AFGHANISTAN TIS', 2018, 0.63), (69, 'AFGHANISTAN TIS', 2018, 5.04), (49, 'AFGHANISTAN TIS', 2018, 3.08), (11, 'AFGHANISTAN TIS', 2018, 0.01), (78, 'AFGHANISTAN TIS', 2018, 0.22), (88, 'AFGHANISTAN TIS', 2018, 91.22), (80, 'AFGHANISTAN TIS', 2018, 0.02), (46, 'AFGHANISTAN TIS', 2018, 0.59), (35, 'AFGHANISTAN TIS', 2018, 1.08), (59, 'AFGHANISTAN TIS', 2018, 7.15), (64, 'AFGHANISTAN TIS', 2018, 80.27), (27, 'AFGHANISTAN TIS', 2018, 0.0), (86, 'AFGHANISTAN TIS', 2018, 0.21), (25, 'AFGHANISTAN TIS', 2018, 5.63), (83, 'AFGHANISTAN TIS', 2018, 0.32), (2, 'AFGHANISTAN TIS', 2018, 0.04), (26, 'AFGHANISTAN TIS', 2018, 0.01), (65, 'AFGHANISTAN TIS', 2018, 0.0), (48, 'AFGHANISTAN TIS', 2018, 1.31), (66, 'AFGHANISTAN TIS', 2018, 2.35), (79, 'AFGHANISTAN TIS', 2018, 8.32), (77, 'AFGHANISTAN TIS', 2018, None), (9, 'AFGHANISTAN TIS', 2018, 0.19), (95, 'AFGHANISTAN TIS', 2018, 0.01), (75, 'AFGHANISTAN TIS', 2018, 0.0), (63, 'AFGHANISTAN TIS', 2018, 2.48), (71, 'AFGHANISTAN TIS', 2018, 0.71), (82, 'AFGHANISTAN TIS', 2018, 0.23), (20, 'AFGHANISTAN TIS', 2018, 2.38), (62, 'AFGHANISTAN TIS', 2018, None), (42, 'AFGHANISTAN TIS', 2018, 46.97), (43, 'AFGHANISTAN TIS', 2018, 39.19), (94, 'AFGHANISTAN TIS', 2018, 0.07), (12, 'AFGHANISTAN TIS', 2018, 0.03), (84, 'AFGHANISTAN TIS', 2018, 1.74), (34, 'AFGHANISTAN TIS', 2018, 0.07), (37, 'AFGHANISTAN TIS', 2018, 3.21), (6, 'AFGHANISTAN TIS', 2018, 43.18), (7, 'AFGHANISTAN TIS', 2018, 89.25), (61, 'AFGHANISTAN TIS', 2018, 21.08), (29, 'AFGHANISTAN TIS', 2018, 18.35), (33, 'AFGHANISTAN TIS', 2018, 0.0), (91, 'AFGHANISTAN TIS', 2018, None), (70, 'AFGHANISTAN TIS', 2018, 0.01), (10, 'AFGHANISTAN TIS', 2018, 0.46), (13, 'AFGHANISTAN TIS', 2018, 0.45), (32, 'AFGHANISTAN TIS', 2018, 0.23), (53, 'AFGHANISTAN TIS', 2018, 2.77), (36, 'AFGHANISTAN TIS', 2018, 0.84), (8, 'AFGHANISTAN TIS', 2018, 61.16), (18, 'AFGHANISTAN TIS', 2018, 0.73), (3, 'AFGHANISTAN TIS', 2018, 38.36), (39, 'AFGHANISTAN TIS', 2018, 0.0), (98, 'AFGHANISTAN TIS', 2018, None), (60, 'AFGHANISTAN TIS', 2018, None), (89, 'AFGHANISTAN TIS', 2018, 2.81), (47, 'AFGHANISTAN TIS', 2018, 1.15), (55, 'AFGHANISTAN TIS', 2018, 10.11), (24, 'AFGHANISTAN TIS', 2018, 13.08), (76, 'AFGHANISTAN TIS', 2018, None), (93, 'AFGHANISTAN TIS', 2018, 16.09), (1, 'AFGHANISTAN TIS', 2018, 0.56), (57, 'AFGHANISTAN TIS', 2018, 7.25), (15, 'AFGHANISTAN TIS', 2018, 0.32), (52, 'AFGHANISTAN TIS', 2018, 0.02), (30, 'AFGHANISTAN TIS', 2018, 0.17), (90, 'AFGHANISTAN TIS', 2018, 0.09), (51, 'AFGHANISTAN TIS', 2018, 1.59), (97, 'AFGHANISTAN TIS', 2018, 0.01), (74, 'AFGHANISTAN TIS', 2018, 0.03), (50, 'AFGHANISTAN TIS', 2018, 0.0), (28, 'ALBANIA', 2018, 0.32), (72, 'ALBANIA', 2018, 0.32), (23, 'ALBANIA', 2018, 0.17), (22, 'ALBANIA', 2018, 0.06), (17, 'ALBANIA', 2018, 4.01), (14, 'ALBANIA', 2018, 0.16), (56, 'ALBANIA', 2018, 0.95), (92, 'ALBANIA', 2018, 0.01), (4, 'ALBANIA', 2018, 0.0), (85, 'ALBANIA', 2018, 0.08), (67, 'ALBANIA', 2018, 0.01), (69, 'ALBANIA', 2018, 0.09), (49, 'ALBANIA', 2018, 0.17), (88, 'ALBANIA', 2018, 0.74)]

conn_norm = create_connection('india_export_norm.db')
cur = conn_norm.cursor()
cur.execute('DROP TABLE EXPORT')
create_table_sql = """CREATE TABLE IF NOT EXISTS [Export] (
    [VALUEID] INTEGER NOT NULL PRIMARY KEY,
    [HSCODE] INTEGER NOT NULL, 
    [COUNTRY] TEXT NOT NULL,
    [YEAR] INTEGER NOT NULL,
    [VALUE] REAL,
    FOREIGN KEY(COUNTRY) REFERENCES Country(COUNTRY),
    FOREIGN KEY(YEAR) REFERENCES YEAR(YEAR),
    FOREIGN KEY(HSCODE) REFERENCES Commodity(HSCODE)
);
"""
create_table(conn_norm, create_table_sql)
sql = ''' INSERT INTO Export (HSCODE, COUNTRY, YEAR, VALUE)
              VALUES(?, ?, ?, ?) '''
cur = conn_norm.cursor()
start_time = time.monotonic() 
cur.executemany(sql, insert_tuples)
conn_norm.commit()
end_time = time.monotonic()     

print(end_time-start_time)

0.2297104510000736

x = 'abcd'
x.upper

<function str.upper()>

conn_norm.commit

<function Connection.commit()>