<jupyter_start><jupyter_text>https://www.dataquest.io/blog/python-pandas-databases/All of the data is in a SQLite database called flights.db, which contains three tables – airports, airlines, and routes. You can download the data https://www.dropbox.com/s/a2wax843eniq12g/flights.db?dl=0<jupyter_code>ls<jupyter_output>Volume in drive C is WINDOWS
 Volume Serial Number is AE83-8D62

 Directory of C:\Users\RCole02.ROYALSURREY\Desktop\GitHub\Useful-python-for-medical-physics\Experiments in ipython notebooks\SQL

04/11/2016  09:39    <DIR>          .
04/11/2016  09:39    <DIR>          ..
04/11/2016  09:39    <DIR>          .ipynb_checkpoints
04/11/2016  09:31         5,415,936 flights.db
04/11/2016  09:38                72 sqlite3 test on airline data.ipynb
               2 File(s)      5,416,008 bytes
               3 Dir(s)  372,543,750,144 bytes free<jupyter_text>Quickstart<jupyter_code>import sqlite3
conn = sqlite3.connect("flights.db")
cur = conn.cursor()<jupyter_output><empty_output><jupyter_text>fetch the first 5 rows from the airlines table:<jupyter_code>cur.execute("select * from airlines limit 5;")<jupyter_output><empty_output><jupyter_text>run another command to actually fetch the results. We can use the fetchall method to fetch all of the results of a query. Returns a list of tuples<jupyter_code>results = cur.fetchall()
print(results)
cur.close()   # Close the connection
conn.close()<jupyter_output><empty_output><jupyter_text>PandasWe can use the pandas read_sql_query function to read the results of a SQL query directly into a pandas DataFrame. It’s highly recommended to use the read_sql_query function when possible.<jupyter_code>import pandas as pd
import sqlite3

conn = sqlite3.connect("flights.db")
df = pd.read_sql_query("select * from airlines limit 5;", conn)
df
df["country"]<jupyter_output><empty_output><jupyter_text>Modifying databasesTo insert a row, we need to write an INSERT query. The below code will add a new row to the airlines table. We specify 9 values to insert, one for each column in airlines. This will add a new row to the table.<jupyter_code>cur = conn.cursor()
cur.execute("insert into airlines values (6048, 19846, 'Test flight', '', '', null, null, null, 'Y')")<jupyter_output><empty_output><jupyter_text>If you try to query the table now, you actually won’t see the new row yet. Instead you’ll see that a file was created called flights.db-journal.  flights.db-journal is storing the new row until you’re ready to commit it to the main database, flights.db.SQLite doesn’t write to the database until you commit a transaction. A transaction consists of 1 or more queries that all make changes to the database at once. This is designed to make it easier to recover from accidental changes, or errors. Transactions allow you to run several queries, then finally alter the database with the results of all of them. This ensures that if one of the queries fails, the database isn’t partially updated.By default, sqlite3 opens a transaction when you do any query that modifies the database. You can read more about it here. We can commit the transaction, and add our new row to the airlines table, using the commit method:<jupyter_code>conn.commit()<jupyter_output><empty_output><jupyter_text>Now, when we query flights.db, we’ll see the extra row that contains our test flight:<jupyter_code>pd.read_sql_query("select * from airlines where id=19846;", conn)<jupyter_output><empty_output><jupyter_text>Passing parameters into a queryIn the last query, we hardcoded the values we wanted to insert into the database. Most of the time, when you insert data into a database, it won’t be hardcoded, it will be dynamic values you want to pass in. These dynamic values might come from downloaded data, or might come from user input.When working with dynamic data, it might be tempting to insert values using Python string formatting: You want to avoid doing this! Inserting values with Python string formatting makes your program vulnerable to SQL Injection attacks. Luckily, sqlite3 has a straightforward way to inject dynamic values without relying on string formatting:<jupyter_code>cur = conn.cursor()
values = ('Test Flight', 'Y')
cur.execute("insert into airlines values (6049, 19847, ?, '', '', null, null, null, ?)", values)
conn.commit()<jupyter_output><empty_output><jupyter_text>Any ? value in the query will be replaced by a value in values. The first ? will be replaced by the first item in values, the second by the second, and so on. This works for any type of query. This created a SQLite parameterized query, which avoids SQL injection issues.We can modify rows in a SQLite table using the execute method:<jupyter_code>cur = conn.cursor()
values = ('USA', 19847)
cur.execute("update airlines set country=? where id=?", values)
conn.commit()
pd.read_sql_query("select * from airlines where id=19847;", conn)<jupyter_output><empty_output><jupyter_text>Creating tables with PandasThe pandas package gives us a much faster way to create tables. We just have to create a DataFrame first, then export it to a SQL table<jupyter_code>from datetime import datetime
df = pd.DataFrame(
    [[1, datetime(2016, 9, 29, 0, 0) , datetime(2016, 9, 29, 12, 0), 'T1', 1]], 
    columns=["id", "departure", "arrival", "number", "route_id"]
)
df<jupyter_output><empty_output><jupyter_text>Then, we’ll be able to call the to_sql method to convert df to a table in a database. We set the keep_exists parameter to replace to delete and replace any existing tables named daily_flights:<jupyter_code>df.to_sql("daily_flights", conn, if_exists="replace")<jupyter_output><empty_output><jupyter_text>We can then verify that everything worked by querying the database:<jupyter_code>pd.read_sql_query("select * from daily_flights;", conn)<jupyter_output><empty_output><jupyter_text>Altering tables with Pandas It’s also possible to use Pandas to alter tables by exporting the table to a DataFrame, making modifications to the DataFrame, then exporting the DataFrame to a table:This code will add a column called delay_minutes to the daily_flights table.<jupyter_code>df = pd.read_sql("select * from daily_flights", conn)
df["delay_minutes"] = None
df.to_sql("daily_flights", conn, if_exists="replace")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08260245574868443, 'fraction_numerical': 0.02726837824908308, 'mean_word_length': 5.20375865479723, 'pattern_counts': {'":': 0, '<': 59, '<?xml version=': 0, '>': 59, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '177557', 'license': 'MIT', 'n_tokens_mistral': 1992, 'n_tokens_neox': 1829, 'n_words': 753, 'path': 'SQL/Assorted/sqlite3 test on airline data.ipynb', 'repo_name': 'afcarl/Useful-python', 'size': '6271'}