def data_to_database():\n data = pandas.read_csv("clorox2020.csv")\n\n # here we establish a connection to the database\n conn = sqlite3.connect("clorox2020")\n # once we have established a connection, we need to create a cursor object\n # the cursor object is the object that we use to execute SQL commands\n cur = conn.cursor()\n\n # here we create a table called stock\n sql_command = "CREATE TABLE clorox (Date text, Open float, High float, Low float, Close float, AdjClose float, Volume int)"\n cur.execute(sql_command)\n\n for i in range(len(data)):\n # insert one row of data into the table per loop\n Date = data["Date"][i]\n Date = "'" + Date + "'"\n Open = data["Open"][i]\n High = data["High"][i]\n Low = data["Low"][i]\n Close = data["Close"][i]\n AdjClose = data["Adj Close"][i]\n Volume = data["Volume"][i]\n\n sql_command = f"INSERT INTO clorox VALUES ({Date}, {Open}, {High}, {Low}, {Close}, {AdjClose}, {Volume})"\n cur.execute(sql_command)\n\n # save all the changes and close the database\n conn.commit()\n conn.close()\n\n\ndef display_data():\n conn = sqlite3.connect("clorox2020")\n cur = conn.cursor()\n sql_command = "SELECT * FROM clorox"\n db_columns = cur.execute(sql_command)\n db_entries = db_columns.fetchall()\n for entry in db_entries:\n print(entry)\n\n conn.close()\n\n\ndata_to_database()\ndisplay_data()<\/code><\/pre><\/div>\n\n\n\nOutput:<\/p>\n\n\n\n
('2020-01-02', 153.380005, 153.410004, 151.550003, 152.610001, 149.262665, 1292500)\n('2020-01-03', 152.570007, 153.880005, 151.240005, 152.910004, 149.556076, 809800)\n('2020-01-06', 153.070007, 153.940002, 152.289993, 153.369995, 150.005981, 869300)\n('2020-01-07', 153.190002, 153.289993, 151.509995, 151.520004, 148.196564, 1198200)\n('2020-01-08', 151.779999, 153.339996, 150.949997, 151.970001, 148.636688, 901200)\n('2020-01-09', 152.229996, 153.009995, 151.720001, 152.800003, 149.448486, 504900)\n......<\/pre>\n\n\n\nNotice that in order to interact with a database, we need to make a connection to that database using conncetion() method. And we need to use cursor() method to create a cursor object to execute some SQL commands. <\/p>\n\n\n\n
Common SQL commands<\/h3>\n\n\n\n
Most of the actions we need to perform on a database are done with SQL statements. SQL statements consists of keywords that are easy to understand. SQL commands are all capitalized (although not necessary) to distinguish them from the Python commands or variable names. The most common operations on a database are CRUD: create, read, update, and delete.<\/p>\n\n\n\n
1. Create<\/p>\n\n\n\n
Syntax: CREATE TABLE <table name> (<cloumn1> <datatype>, <cloumn2> <datatype>, …)<\/p>\n\n\n\n
In this command, neither the table name nor column name needs to covered by quotation marks. For the datatype, in SQLite text is for string, float is for floating number, int is for integer number, and etc.<\/p>\n\n\n\n
When creating a table, if the table we are creating exists, the creation will raise an error. In order to avoid this error we can add [IF NOT EXISTS] before the table name.<\/p>\n\n\n\n
Special note: we can actually create multiple tables in a single database, but the table names should be different.<\/p>\n\n\n\n
# suppose we would like to create another table under the database clorox2020\n\ndef another_table():\n conn = sqlite3.connect("clorox2020")\n cur = conn.cursor()\n sql_comm = "CREATE TABLE IF NOT EXISTS buyers(buyer_id text, amount int)"\n cur.execute(sql_comm)\n cur.execute("INSERT INTO buyers VALUES ('75c', 13)")\n cur.execute("INSERT INTO buyers VALUES ('16d', -4)")\n cur.execute("INSERT INTO buyers VALUES ('38a', 10)")\n cur.execute("INSERT INTO buyers VALUES ('59c', -9)")\n cur.execute("INSERT INTO buyers VALUES ('66b', 37)")\n cur.execute("INSERT INTO buyers VALUES ('31a', -43)")\n conn.commit()\n\n cur.execute("SELECT * FROM buyers")\n results = cur.fetchall()\n for result in results:\n print(result)\n\n conn.close()<\/code><\/pre><\/div>\n\n\n\nOutput:<\/p>\n\n\n\n
('75c', 13)\n('16d', -4)\n('38a', 10)\n('59c', -9)\n('66b', 37)\n('31a', -43)\n<\/pre>\n\n\n\n2. Read<\/p>\n\n\n\n
Syntax: SELECT <*\/column> FROM <table> [WHERE <condition>]<\/p>\n\n\n\n
Using this command, we can “read” some data from a table. In column, we can use “*” to select ALL cloumns or type the specific column name to query the data from that columns. We can also select some statistics data like SUM(), AVG(), and etc. In addition, we can use some conditions to somehow filter the data, such as WHERE [column >\/< some value], or WHERE [column = some value]. <\/p>\n\n\n\n
# get the data form the database\ndef get_some_data():\n conn = sqlite3.connect("clorox2020")\n cur = conn.cursor()\n volume_max = cur.execute("SELECT MAX(Volume) FROM clorox").fetchone()[0]\n volume_min = cur.execute("SELECT MIN(Volume) FROM clorox").fetchone()[0]\n volume_avg = cur.execute("SELECT AVG(Volume) FROM clorox").fetchone()[0]\n\n print(f"The maximum volume is:{volume_max}")\n print(f"The minimum volume is:{volume_min}")\n print(f"The average volume is:{volume_avg}")\n\n conn.close()<\/code><\/pre><\/div>\n\n\n\nOutput:<\/p>\n\n\n\n
The maximum volume is:7478700\nThe minimum volume is:489500\nThe average volume is:1776609.865470852<\/pre>\n\n\n\n3. Update<\/p>\n\n\n\n
Syntax: UPDATE <table> SET <cloumn> = <some value> [WHERE <condition>]<\/p>\n\n\n\n
Using this command, we can modify the value of some columns in our table to the value that we want. It’s important to notice that if we don’t use “WHERE” to specify the rows, the whole column will be changed to that value. On the other hand, we can use the “WHERE” command to modify more than one line.<\/p>\n\n\n\n
# update the clorox table and double the colume\ndef update_volume():\n conn = sqlite3.connect("clorox2020")\n cur = conn.cursor()\n \n cur.execute("UPDATE clorox SET Volume = Volume * 2")\n conn.commit()\n conn.close()<\/code><\/pre><\/div>\n\n\n\n4. Delete<\/p>\n\n\n\n
Syntax: DELETE FROM <table> [WHERE <condition>]<\/p>\n\n\n\n
Using this command, we can “delete” some data from our table. Similar to “UPDATE” command, when no condition is assigned, the whole table will be deleted. Therefore, it’s always a good idea to specify the row that we want to delete using “WHERE” command. And the we can also delete more than one line using the “WHERE” command.<\/p>\n\n\n\n
# delete the rows which contains open price >= 200\ndef delete_open():\n conn = sqlite3.connect("clorox2020")\n cur = conn.cursor()\n cur.execute("DELETE FROM clorox WHERE Open > 200")\n conn.commit()\n conn.close()<\/code><\/pre><\/div>\n\n\n\nExecute the commands<\/h3>\n\n\n\n
After typing the commands that we would like to use, the next step is executing them. To execute the commands, we need to use cur.execute(sql_command)<\/code>. For the “SELETE” command, we need to use fetchall() to get all the row or fetchone() to get one row. The fetchall() function will return a list object, we can use a for loop to iterate it. The fetchone() function will return a tuple object of the data. For the “CREATE”, “UPDATE”, and “DELETE” commands, we need to use conn.commit()<\/code> to commit the changes that we make. Otherwise, the changes WILL NOT<\/strong> be saved to the table. In the end of all the command, if we no longer want to interact with the database, we should use conn.close()<\/code> to close the connection to the database to avoid memory leak.<\/p>\n","protected":false},"excerpt":{"rendered":"SQL stands for “structured query language”. It is a programming language for storing and processing information in a relational database. A relational database stores information in tabular form, with rows and columns representing different data attributes and the various relationships between the data values.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-252","post","type-post","status-publish","format-standard","hentry","category-python"],"_links":{"self":[{"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=\/wp\/v2\/posts\/252","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=252"}],"version-history":[{"count":5,"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=\/wp\/v2\/posts\/252\/revisions"}],"predecessor-version":[{"id":267,"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=\/wp\/v2\/posts\/252\/revisions\/267"}],"wp:attachment":[{"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=252"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=252"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.baizhao666.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=252"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}