1

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()
Improve this question
3
  • Your question seems a little broad to me, as there are a few steps involved in what you are wanting to do, and likely a few different ways you could achieve what you want. But basically there are three steps to what you want to do: Read the lines of the file, Process each of the lines to extract the text you want, and Insert the extracted text into the database. Is there a particular step you're having trouble with? Commented Apr 24, 2019 at 15:11
  • Right now it is how I use what @zedfoxus and do it for multiple lines, like what statement and how do I use it? He did the kernel_version, now what statement would be best to do the next line for example Product_type? Commented Apr 25, 2019 at 12:59
  • Updated what I am trying in my post Commented Apr 25, 2019 at 13:06

1 Answer 1

Reset to default
2

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')
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This definitely helped steer me in the right direction! Now how do I go about now inserting the next line in? What statements will help me continue writing into database? Could you provide an example, please! Great help! So far so good! Understanding this!
Updated what I am trying in my post.
@olorid what do you mean by next lines? You want to enter product type in product_type and product version in product_version fields?
yes! How to continue after the if statement to enter more info from next lines like product type into the product_version in sqlite, I am using the elif statement, not sure if I am doing that right, since I get errors, Or how I can accomplish that.
That's great to hear that you tried a couple things. Very good. Not in this question but future questions, feel free to update the question with things you have tried. It shows the audience how you are focusing your efforts. Hopefully, this example will get you going. You can put a closure to your ticket once you are satisfied by either waiting for additional answers or marking one of them as accepted.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.