<jupyter_start><jupyter_text>SQL - example SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. * SQLite is often the technology of choice for small applications, particularly those of embedded systems and devices like phones and tablets, smart appliances, and instruments.* It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.The sqlite3 module in the Python standard library provides a SQL interface to communicate with databases.https://docs.python.org/3/library/sqlite3.htmlCreating a new SQLite database is as simple as creating a `connection` using the `sqlite3` module in the Python standard library. To establish a connection all you need to do is pass a file path to the `connect(...) method` in the sqlite3 module, and if the database represented by the file does not exists one will be created at that path.Once you have a `Connection`, you can create a `Cursor` object and call its execute() method to perform SQL commands.`Cursor` objects represent a database cursor, which is used to manage the context of a fetch/retrieval operation.A call to the `Cursor`'s execute() method is used to perform SQL commands.<jupyter_code>from sqlite3 import connect

'''    
    Establish a connection to the database.
    This statement creates the file: example_gene_ann.sqlite , in the current directory if it does not exist.
    The file was not provided so it should be created by this statement.
'''

connection = connect('example_gene_ann.sqlite')<jupyter_output><empty_output><jupyter_text>When you are running code in this notebook and want to start again, restart the kernel.  Aslo, to start again with an empty database delete the file: `example_gene_ann.sqlite`<jupyter_code>cursor = connection.cursor()
sql = '''SELECT * FROM sqlite_master;'''
cursor.execute(sql)

# retrieves the result of the execution of the sql statement 
# in this case from an empty database

cursor.fetchall()
# what attributes and methods does connection have?
print("CONNECTION knows/has:")
for elem in dir(connection):
    if not elem.startswith("__"):
        print(elem)

print()

# what attributes and methods does the cursor have?
print("CURSOR knows/has:")
for elem in dir(cursor):
    if not elem.startswith("__"):
        print(elem)<jupyter_output><empty_output><jupyter_text>CREATE TABLE  - statementhttps://www.sqlitetutorial.net/sqlite-create-table/```sqlCREATE TABLE [IF NOT EXISTS] [schema_name].table_name (    column_1 data_type PRIMARY KEY,    column_2 data_type NOT NULL,    column_3 data_type DEFAULT 0,    table_constraints) [WITHOUT ROWID];```In this syntax:* First, specify the name of the table that you want to create after the CREATE TABLE keywords. The name of the table cannot start with sqlite_ because it is reserved for the internal use of SQLite.* Second, use `IF NOT EXISTS` option to create a new table if it does not exist. Attempting to create a table that already exists without using the IF NOT EXISTS option will result in an error.* Third, optionally specify the schema_name to which the new table belongs. The schema can be the main database, temp database or any attached database.* Fourth, specify the column list of the table. Each column has a name, data type, and the column constraint. SQLite supports `PRIMARY KEY, UNIQUE, NOT NULL`, and `CHECK` column constraints.* Fifth, specify the table constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints.* Finally, optionally use the `WITHOUT ROWID` option. By default, a row in a table has an implicit column, which is referred to as the rowid, oid or _rowid_ column. The rowid column stores a 64-bit signed integer key that uniquely identifies the row inside the table. If you don’t want SQLite creates the rowid column, you specify the WITHOUT ROWID option. A table that contains the rowid column is known as a rowid table. Note that the WITHOUT ROWID option is only available in SQLite 3.8.2 or later. https://www.sqlite.org/syntaxdiagrams.htmlconflict-clause Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:https://www.sqlite.org/datatype3.html* `NULL`. The value is a NULL value.* `INTEGER`. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.* `REAL`. The value is a floating point value, stored as an 8-byte IEEE floating point number.* `TEXT`. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).* `BLOB`. The value is a blob of data, stored exactly as it was input.  A `PRIMARY KEY` is a very important concept to understand.  * It is the designation for a column or a set of columns from a table.* It is recommended to be a serial value and not something related to the business needs of the data in the table.* A primary key is used to uniquely identify a row of data; combined with a column name, uniquely locates a data entry* A primary key by definition must be `UNIQUE` and `NOT NULL` * The primary key of a table, should be a (sequential) non-repeating and not null value  * Primary keys are generally identified at time of table creation  * A common method for generating a primary key, is to set the datatype to `INTEGER` and declare `AUTOINCREMENT` which will function when data is inserted into the table* Primary keys can be a composite of 2 or more columns that uniquely identify the data in the table  A `FOREIGN KEY` is a column(s) that points to the `PRIMARY KEY` of another table * The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.Only the values that exist in the `PRIMARY KEY` column are allowed to be present in the FOREIGN KEY column.Example: A `gene` table has the `PRIMARY KEY` `gene_id`. The GO2_gene GO term is associated with a geneThey are also the underpinning of how tables are joined and relationships portrayed in the database The `sqlite_master` has the following create statement: ```sqlCREATE TABLE sqlite_master ( type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT );```  Create the table `gene` with the columns: `gene_id`, `gene_symbol`. `gene_name`   The `connection` object methods can be used to save or revert/reset the changes after a command that makes changes to the database `COMMIT` - save the changes  `ROLLBACK` - revert the changes<jupyter_code>sql = ''' 
CREATE TABLE IF NOT EXISTS gene (
    gene_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    gene_symbol TEXT(10) NOT NULL DEFAULT "",
    gene_name TEXT(50) 
);
'''
try:
    cursor.execute(sql)
except connection.DatabaseError:
    print("Creating the table gene raised a database error!")
    connection.rollback()
    raise
else:
    connection.commit()
finally:
    print("done!")<jupyter_output><empty_output><jupyter_text>Similar error handling, as seen above, can be when executing any statement that changes the database.  Check if the new table appears in the `sqlite_master` table<jupyter_code>sql = '''
SELECT name, sql
FROM sqlite_master 
WHERE name= "gene"
LIMIT 2;
'''
cursor.execute(sql)
print(cursor.fetchone())<jupyter_output><empty_output><jupyter_text>The `sqlite_sequence` table is created and initialized automatically whenever a regular table is created if it has a column with the `AUTOINCREMENT` option set.https://www.sqlite.org/autoinc.html ________ Helper methods allow for a nicer display of the result<jupyter_code>def get_header(cursor):
    '''
    Makes a tab delimited header row from the cursor description.
    Arguments:
        cursor: a cursor after a select query
    Returns:
        string: A string consisting of the column names separated by tabs, no new line
    '''
    return '\t'.join([row[0] for row in cursor.description])

def get_results(cursor):
    '''
    Makes a tab delimited table from the cursor results.
    Arguments:
        cursor: a cursor after a select query
    Returns:
        string: A string consisting of the column names separated by tabs, no new line
    ''' 
    res = list()
    for row in cursor.fetchall():        
        res.append('\t'.join(list(map(str,row))))
    return "\n".join(res)<jupyter_output><empty_output><jupyter_text>Important for homework 8 Different ways to retrieve results - observe the different data structures displayed<jupyter_code>sql = '''SELECT type, name FROM sqlite_master;'''

cursor.execute(sql)
print("Iterate through the cursor:")
for row in cursor: 
    print(row)
    
print()

cursor.execute(sql)
print("Use the Cursor fetchall() method:")
print(cursor.fetchall())

print()

cursor.execute(sql)
print("Use the get_results method, which uses fetchall() and displays the output in a tabular format:")
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>____________  Back to the CREATE TABLE - let's do another one<jupyter_code>sql = '''
CREATE TABLE IF NOT EXISTS gene2_GO (
    gene_id INTEGER NOT NULL,
    go_term_id TEXT(10) DEFAULT "" NOT NULL,
PRIMARY KEY (gene_id,go_term_id)
FOREIGN KEY (gene_id) REFERENCES  gene  (gene_id)
) WITHOUT ROWID;
'''
try:
    cursor.execute(sql)
except connection.DatabaseError:
    print("Creating the table gene raised a database error!")
    connection.rollback()
    raise
else:
    connection.commit()
finally:
    print("done!")<jupyter_output><empty_output><jupyter_text>Check if the new table appears in the `sqlite_master` table<jupyter_code>sql = '''
SELECT name, sql
FROM sqlite_master 
WHERE type= "table";
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>INDEXINGIndexes are lookup table, like the index of a book.They are usually created for columns that have unique/ or less redundant values and provide a way to quicky search the values.Indexing creates a copy of the indexed columns together with a link to the location of the additional information. The index data is stored in a data structure that allows for fast sorting. E.g.: balanced-tree - every leaf is at most n nodes away from the root) that allows for fast sorting. All queries (statements) regarding an indexed table are applied to the index* One important function in Relational Databases is to be able to create indexes on columns in tables  * These indexes are pre-calculated and stored in the database * Indexes should be created on columns that are used in queries and joins   * They will rapidly speed up query return rate and improve query performanceTo create an index use the following command:```sqlCREATE INDEX indexName ON tableName (columnName)```<jupyter_code>sql = '''
CREATE INDEX gene_id_idx 
ON gene (gene_id)
'''
cursor.execute(sql)
connection.commit()<jupyter_output><empty_output><jupyter_text>Check if the new index appears in the `sqlite_master` table<jupyter_code>sql = '''
SELECT name, sql
FROM sqlite_master 
WHERE type= "index";
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>Remove the index<jupyter_code>sql = '''
DROP INDEX gene_id_idx 
'''
cursor.execute(sql)
connection.commit()<jupyter_output><empty_output><jupyter_text>Check if the index was removed from the `sqlite_master` table<jupyter_code>sql = '''
SELECT name, sql
FROM sqlite_master 
WHERE type= "index";
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>INSERT - statementMakes changes to the database tableAdds new data to a table (if the constraints are met)Constraint examples: * For one designated column or a group of columns that are designated as Primary Key the values are unique* The value inserted in a column that has a Foreign Key constraint should exist in the column that it refers to```sqlINSERT INTO  (, , ) VALUES (value1, value2, value3);``` One simple INSERT command adds 1 row of data at a time into an existing table   Connection object allows us to:*  COMMIT - save the changes *  ROLLBACK - reverts/discards the changes  Check for tables in the `sqlite_master` table<jupyter_code>sql = '''
SELECT name, type
FROM sqlite_master 
WHERE type= "table";
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>Let's see what is in the `gene` table (it should be nothing):<jupyter_code>sql = '''
SELECT *
FROM gene ;
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>Let's try an insert:```sqlINSERT INTO  (, , ) VALUES (value1, value2, value3);```<jupyter_code>sql = '''
INSERT INTO gene (gene_symbol, gene_name) 
VALUES ("WNT1","Wnt Family Member 1");
'''
cursor.execute(sql)
connection.commit()<jupyter_output><empty_output><jupyter_text>We have a gene in the table!!! And the gene_id was automatically generated.<jupyter_code>sql = '''
SELECT *
FROM gene ;
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>Now let's do another insert and use a list to provide the values.<jupyter_code>values_list = ["VEGFA","vascular endothelial growth factor A"]

sql = f'''
INSERT INTO gene (gene_symbol, gene_name) 
VALUES (?,?);
'''

cursor.execute(sql, values_list)
connection.commit()<jupyter_output><empty_output><jupyter_text>See the rows from the table<jupyter_code>sql = '''
SELECT *
FROM gene ;
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>Now let's do another insert and retrieve the gene_id<jupyter_code>sql = '''
INSERT INTO gene (gene_symbol, gene_name) 
VALUES ("EGFR","Epidermal growth factor receptor");
'''
cursor.execute(sql)
connection.commit()

# This command retrieves the identifier of the last row from the most current query
gene_id_value = cursor.lastrowid
gene_id_value<jupyter_output><empty_output><jupyter_text>Now, let's insert an element in the `gene2_GO` table  Let's see what is in the `gene2_GO` table first (it should be nothing):<jupyter_code>sql = '''
SELECT *
FROM gene2_GO ;
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))
values_tuple = (gene_id_value, "GO:0005739")
sql = f'''
INSERT INTO gene2_GO (gene_id, go_term_id) 
VALUES (?, ?);
'''
cursor.execute(sql,values_tuple)
connection.commit()<jupyter_output><empty_output><jupyter_text>Let's see the row in the table<jupyter_code>sql = '''
SELECT *
FROM gene2_GO ;
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>INSERT another row Aslo if we provide all required elements in order there is no need to specify the columns<jupyter_code>sql = f'''
INSERT INTO gene2_GO  
VALUES ("{gene_id_value}","GO:1904658");
'''
cursor.execute(sql)
connection.commit()<jupyter_output><empty_output><jupyter_text>Retrieve the rows<jupyter_code>sql = '''
SELECT *
FROM gene2_GO ;
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>You can have a Python "table" structure (list of lists) of insert values and get them all inserted in one command, each sublist having the correct number of values.```sqlcursor.executemany(sql,values_tbl)```  Sub-query - slect in select<jupyter_code>sql = '''
SELECT gene_symbol
FROM gene 
WHERE gene_id IN
    (SELECT DISTINCT gene_id FROM gene2_GO);
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))<jupyter_output><empty_output><jupyter_text>To remove a table use the `DROP TABLE` statement<jupyter_code>sql = '''
DROP TABLE IF EXISTS gene2_GO;
'''
cursor.execute(sql)
connection.commit()
sql = '''
SELECT name AS "TABLE NAME"
FROM sqlite_master ;
'''
cursor.execute(sql)
print(get_header(cursor))
print(get_results(cursor))
# And close()

cursor.close()
connection.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08596024029231436, 'fraction_numerical': 0.004459032637641667, 'mean_word_length': 5.302888368462139, 'pattern_counts': {'":': 0, '<': 101, '<?xml version=': 0, '>': 101, 'https://': 5, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '433351', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 4777, 'n_tokens_neox': 4585, 'n_words': 1929, 'path': 'classes/SQL_example.ipynb', 'repo_name': 'betteridiot/b575f19', 'size': '16147'}