Python program, inserting txt file to sqlite3 database

Question

Currently working on a program in Python that has to take data from a text file and input it into appropriate place in SQLite. I have created my database and the columns, now I am stuck on how I process the text data in and read it into the sqlite database.

Here are a couple lines from text file.

Kernel version:            Windows 10 Enterprise, Multiprocessor Free
Product type:              Professional
Product version:           6.3
Service pack:              0

Here is what I have so far,

import sqlite3                                                      
conn = sqlite3.connect('systeminfo.db')                                    
c = conn.cursor()                       

def create_table():                                                   
    c.execute("""CREATE TABLE IF NOT EXISTS system_information (      
                Machine_Name text,                                    
                Kernel_version text,                                  
                Product_type text,                                    
                product_version text,                                 
                Registered_organization text,                         
                registered_owner text,                                
                system_root text,                                     
                processors text,                                      
                physical_memory text                                  
                )""")                                                 
create_table1()                                                                    

This creates my database and my table just how I want it, now I am stuck on taking the for example Kernel version from text file and putting the "Windows 10 Enterprise" into the database under the Kernel_Version Column.

UPDATE:

After using @zedfoxus tips, I was able to successfully get data, here is what I have, now how can I do the next lines more efficient? I am using elif, getting errors,

def insert_data(psinfo):                                                                             
    with open(psinfo) as f:                                                                          
            file_data = f.readlines()                                                                
    for item in file_data:                                                                           
           if 'Kernel version' in item:                                                              
               info = item.strip().split(':')                                                        
               val = info[1].strip().split(',')                                                      

           elif 'Product type' in item:                                                              
             info = item.strip().split(':')                                                          
             val = info[1].strip().split(',')                                                        
             c.execute(                                                                              
                 'INSERT INTO system_information (Kernel_version,Product_type ) values(?,?)',        
                 (val[1].strip(),)                                                                   
             )                                                                                       
             conn.commit()         

Answer 1

Let's say you have a file called kernel.txt that contains

Kernel version:            Windows 10 Enterprise, Multiprocessor Free
Product type:              Professional
Product version:           6.3
Service pack:              0

Your python code would just have to read that text file and insert data into SQLite like so:

import sqlite3
conn = sqlite3.connect('systeminfo.db')
c = conn.cursor()

def create_table():
    # same thing you had...just removing it for brevity

def insert_data(filename):

    # read all the lines of the file
    with open(filename) as f:
        file_data = f.readlines()

    # if Kernel version exists in the line, split the line by :
    # take the 2nd item from the split and split it again by ,
    # take the first item and pass it to the insert query
    # don't forget to commit changes
    for item in file_data:
        if 'Kernel version' in item:
            info = item.strip().split(':')
            val = info[1].strip().split(',')
            c.execute(
                'insert into system_information (Kernel_version) values(?)',
                (val[0].strip(),)
            )
            conn.commit()

create_table()
insert_data('kernel.txt')

You will have to change this code if you have multiple files containing such information, or if you have a single file containing multiple blocks of similar information. This code will get you started, though.

Update

I have separated the data parsing into its own function that I can call multiple times. Note how I have created 3 variables to store additional information like product type and version. The insert execution is happening outside of the loop. We are, basically, collecting all information we need and then inserting in one shot.

import sqlite3
conn = sqlite3.connect('systeminfo.db')
c = conn.cursor()

def create_table():
    # same thing you had...just removing it for brevity
    pass

def get_value(item):
    info = item.strip().split(':')
    val = info[1].strip().split(',')
    return val[0].strip()

def insert_data(filename):

    # read all the lines of the file
    with open(filename) as f:
        file_data = f.readlines()

    # if Kernel version exists in the line, split the line by :
    # take the 2nd item from the split and split it again by ,
    # take the first item and pass it to the insert query
    # don't forget to commit changes
    kernel_version = ''
    product_type = ''
    product_version = ''

    for item in file_data:
        if 'Kernel version' in item:
            kernel_version = get_value(item)
        elif 'Product type' in item:
            product_type = get_value(item)
        elif 'Product version' in item:
            product_version = get_value(item)

    c.execute(
        '''insert into system_information
        (Kernel_version, Product_type, Product_version)
        values(?, ?, ?)''',
        (kernel_version, product_type, product_version,)
    )
    conn.commit()

create_table()
insert_data('kernel.txt')