In Tutorial 5 we inserted some sample data into our table named test_table in our test.db in sqlite. We read the data from a file named book1.xls and we used the JXL library to accomplish our task.
Now let’s do the same in Python. We will need to import a package called xlrd in order to read excel data using Python. Also note that we can read both xls and xlsx files using this xlrd package. To import the package open a command prompt and
type:
pip install xlrd
Remember we did the same when installing flask in tutorial 6.
We will also need to copy test.db and sqlite 3 into our Python workspace. Go back to tutorial 4 where I showed u how to find our current workspace and how to copy our database file into that workspace. We need to do this because in tutorial 4 we inserted sample data into our test_table and for this tutorial we will be using the same table and the same test.db schema so by copying our schema over to our current python workspace the program will be able to insert into the same test_table.
Okay now that we got our environment set up, create a new PyDev Module. I named mine as read_excel.
Copy and paste the following code into your new module and save. Notice that reading excel using Python requires many less steps than does Java
import xlrd
import sqlite3
# Open the workbook and define the worksheet
book = xlrd.open_workbook("C:/users/rickd/Desktop/files/book1.xls")
#assign the variable first_sheet and it gets first sheet without knowing its name
first_sheet = book.sheet_by_index(0)
num_rows = first_sheet.nrows
num_cols = first_sheet.ncols
print ("number of rows " , num_rows)
print ("number of cols " , num_cols)
# Establish an SQLite connection to test.db located in current workspace
connection = sqlite3.connect('test.db')
# Get the cursor, which is used to traverse the database, line by line
cursor = connection.cursor()
# Create the INSERT INTO sql query
query = """INSERT INTO test_table (column1, column2, column3, column4) VALUES (?, ?, ?, ?)"""
# Create a For loop to iterate through each row in the XLS file, starting at row 1 position 0
for row in range(0, first_sheet.nrows): #starting position for row is 0...loop for n number of rows in table
value_for_col_1 = first_sheet.cell(row,0).value #get val at each location, then go to next row and do the same
value_for_col_2 = first_sheet.cell(row,1).value
value_for_col_3 = first_sheet.cell(row,2).value
value_for_col_4 = first_sheet.cell(row,3).value
# Assign values from each row
values = (value_for_col_1, value_for_col_2, value_for_col_3, value_for_col_4)
# Execute sql Query.....put assigned vals into query
cursor.execute(query, values)
# Close the cursor
cursor.close()
# Commit the transaction
connection.commit()
# Close the database connection
connection.close()
Click run as Python run..u see number of rows and columns printed in the console.
That’s it we are done. Now go back to your workspace and double click on sqlite3 and then .open test.db and then select * from test_table. We will see that the data is inserted. Tutorial 4 explained how to do this part
Click here for intro blog about author Rick Delpo