To that end, this exercise is intended to demonstrate how an Excel workbook can easily be turned into a SQLite database file. At the start of this post, I mentioned that the structure of an Excel workbook is similar to a SQL database in that both can be comprised of multiple related tables. One parting word of clarification before we close. No need to establish a new cursor if you are not writing data to the database. If you want to work with this database again after closing, just reestablish the database connection db_conn = nnect("data/superstore.db")Īnd run queries using read_sql. When you are finished working on this database in Python you can close the connection by running db_conn.close(). To briefly illustrate how joining works, consider how we can add OrderDate to the sales table by “joining” on OrderID, as illustrated below.
For example, someone who is familiar with SQL concepts but unfamiliar with this particular data could take a look at the CREATE TABLE statement that creates the sales table and recognize that data from the sales and orders tables can be brought together, or joined, by matching on values in the OrderID columns from both tables. Line #11 from the CREATE TABLE statement follows the same logic for the ProductID column.īy specifying the primary and foreign keys, we are able to create a “map” that shows how the tables in the database relate to each other. The second part, REFERENCE orders(OrderID) then specifies the table and primary key to which OrderID refers.
The first part of this code, FOREIGN KEY(OrderID), establishes that the OrderID column in sales is a foreign key.
#Sqlitestudio data type size code#
Let’s return to the actual code in line #10 in the previous CREATE TABLE statement. Before we move further, however, let’s take a look at the actual SQL code that will be used to create the sales table to better understand the table creation process. Later on, we will populate our tables with the data from the four previously-created data frames. The next step is to create the tables that will be included in the database, though it is important to emphasize that this next step of creating the tables will result in empty tables. Here, the cursor is assigned to a variable I named c. We now need to establish a cursor object, which is a tool that executes SQL code against the database of interest. Cool! But wait…how do we actually run the SQL code that will create the data tables? Glad you asked. db_conn is also pointing to the superstore.db database and can be thought of as an interface for working with this database. The newly created superstore.db file is an empty SQLite database at this point (i.e., it has no tables). When this code above is executed, a new file named superstore.db will be created in the data directory (assuming superstore.db does not already exist in this directory).
We can initiate a new SQLite database connection object and assign this object to a variable. This is a lightweight SQL database engine that can be used to create databases stored as files on one’s personal computer. There are multiple relational database products available and the specific product we will use here is SQLite. Reading The Data In Pythonįirst, if you have not installed the xlrd package using condaor pip, do so before starting a Python session, or else you will encounter the following error when trying to read in the Excel file (even with pandas installed). This dataset is stored as an Excel workbook and contains example sales transaction data stored across the following four sheets: sales, orders, customers, and products. The example in this post uses data from the Superstore-Sales dataset, which can be found here. With that thought in mind, this post moves through an example using Python to transform an Excel spreadsheet into a database that can be queried using Structured Query Language (SQL). In this way, a relational database is not totally dissimilar from an Excel workbook with related datasets stored across multiple worksheets. Transforming spreadsheets into queryable database tables IntroductionĪ relational database is a collection of data tables - sets of rows and columns that store individual pieces of data - that can be connected to each other.
0 kommentar(er)
