{"id":33,"date":"2022-02-27T20:01:44","date_gmt":"2022-02-27T20:01:44","guid":{"rendered":"https:\/\/sonic.fabio.org.uk\/?p=33"},"modified":"2022-02-27T22:02:23","modified_gmt":"2022-02-27T22:02:23","slug":"sqlite-in-python","status":"publish","type":"post","link":"https:\/\/sonic.fabio.org.uk\/?p=33","title":{"rendered":"SQLite in Python"},"content":{"rendered":"\n

Getting started<\/h2>\n\n\n\n

SQLite is a common SQL database engine. In this blog, I will go over how you can connect and query a SQLite database in Python.<\/p>\n\n\n\n

Let’s import the necessary libraries for this example, and establish a connection to a database. We will be using the sqlite3 <\/code>library and pandas<\/code>.<\/p>\n\n\n\n

import sqlite3\nimport pandas as pd\nconn = sqlite3.connect('supermarket.db') # connect to a supermarket database<\/code><\/pre>\n\n\n\n
To execute SQL commands, we need to create a cursor object. Let’s use the cursor to create a table called shops <\/em>which will contain basic information about our supermarket stores (shop ID, size, postcode) . The store sizes are small, medium and large.<\/p>\n\n\n\n
cursor = conn.cursor() # create a cursor\ncursor.execute("CREATE TABLE shops(shopid VARCHAR(100) PRIMARY KEY, Store_size VARCHAR(30), Postcode VARCHAR(8))")<\/code><\/pre>\n\n\n\n
So, cursor.execute()<\/code> executes SQL commands.<\/p>\n\n\n\n
We can insert data into the table using the INSERT<\/code> command. If we do this, use conn.commit()<\/code> to save the data to the table.<\/p>\n\n\n\n
However, in this example, we have a pandas<\/code> DataFrame called shops_df<\/code> that we wish to insert into our SQL table called shops<\/code>. It has the same columns as our shops<\/code> table. If you’re more familiar with pandas than SQL like me, I will use pandas to clean data first. Only after this will I want to store the data in an SQL database.<\/p>\n\n\n\n
The to_sql() <\/code>function allows us to write records in a DataFrame to an SQL database.<\/p>\n\n\n\n
shops_df.to_sql('shops', conn, if_exists='append', index=False) # write shops_df to shops table<\/code><\/pre>\n\n\n\n
Queries<\/h2>\n\n\n\n
The cursor behaves like an iterator object. We can use a for loop<\/em> to retrieve all rows from a SELECT statement. To make it easier to query in future, I’ll create a query function.<\/p>\n\n\n\n
def query(q):\n    try:\n        for row in cursor.execute(q):\n            print(row)\n    except Exception as e:\n        print("Something went wrong... ", e)\n\nquery('SELECT * FROM shops')<\/code><\/pre>\n\n\n\n
We can also save the results of a query by assigning it to a variable. The variable will be a list of tuples. A tuple for each row.<\/p>\n\n\n\n
q="SELECT shopid, Postcode FROM shops"\nquery_results = cursor.execute(q).fetchall() # save results as a list of tuples<\/code><\/pre>\n\n\n\n
Storing results from a query like this can allow you to represent table data with graphs. For example, if you had a table called sales<\/code> in your database which has the shop profits, you could plot trends in sales. I will illustrate this now.<\/p>\n\n\n\n
\"\"
Above: A preview of the sales table which is in our SQLite database already<\/figcaption><\/figure>\n\n\n\n
Let’s query the minimum, mean and maximum yearly sales…<\/p>\n\n\n\n
c="""\nSELECT shops.Store_size, sales.Year, MIN(sales.Sales), AVG(sales.Sales), MAX(sales.Sales)\nFROM shops\nJOIN sales\n  ON shops.shopid = sales.shopid\nGROUP BY shops.Store_size, sales.Year \n"""\nquery(c)\nresult = cursor.execute(c).fetchall()<\/code><\/pre>\n\n\n\n
\"\"
Above: the results from our query<\/figcaption><\/figure>\n\n\n\n
…next I plot the mean sales for medium sized stores against the year…<\/p>\n\n\n\n
years=[] # store the year\nmeans = [] # store the mean sales\nsize = 'medium'\nfor results in result:\n    if results[0] == size:\n        means.append(results[3])\n        years.append(results[1])\n\nplt.plot(years, means, linestyle = 'dashed', marker = 'o', ms= '5')\nplt.title(f"{size} store mean sales")\nplt.xlabel("year")\nplt.ylabel("sales")\nplt.savefig(f"{size}_store_mean_sales.png")\nplt.show()<\/code><\/pre>\n\n\n\n
\"\"
Above: the mean sales for stores that are medium sized<\/figcaption><\/figure>\n\n\n\n
Now we are done, close the connection to the database.<\/p>\n\n\n\n
conn.close() # don't forget to close the connection afterwards<\/code><\/pre>\n\n\n\n
I hope this blog was helpful for getting started with the sqlite3 library, to connect to a database, and use a cursor to execute SQL commands in python.<\/p>\n","protected":false},"excerpt":{"rendered":"
Getting started SQLite is a common SQL database engine. In this blog, I will go over how you can connect and query a SQLite database in Python. Let’s import the necessary libraries for this example, and establish a connection to a database. We will be using the sqlite3 library and pandas. To execute SQL commands, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[8,12],"tags":[11,10],"_links":{"self":[{"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=\/wp\/v2\/posts\/33"}],"collection":[{"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=33"}],"version-history":[{"count":8,"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=\/wp\/v2\/posts\/33\/revisions"}],"predecessor-version":[{"id":48,"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=\/wp\/v2\/posts\/33\/revisions\/48"}],"wp:attachment":[{"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=33"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=33"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sonic.fabio.org.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=33"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}