<jupyter_start><jupyter_text>Using SQL Databases in PythonYou can, of course, access databases from your code, not just from Jupyter or the command line.We will first create a database, and then access it using a Python SQL library.  <NAME> AUTOCOMMIT ! remember: docker start course-mysql<jupyter_code>%load_ext sql
#%config SqlMagic.autocommit=False 

## good idea to leave autocommit on
## if we mess up it is easier to drop, create and retry
## in a production situation, if we were going to work a database that the public was using
## THAT WE SHOULDNT DO, WE SHOULD DO IT WITH A BACKUP, there autocommit off is better

%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql
            
# here we are using sqlMagic
%sql show databases;
# if you have the germplasm database, please drop it now!
# if not, then move to the next box

%sql drop database germplasm
%sql drop database testing123
%sql create database germplasm;
%sql show databases
%sql use germplasm;
#%sql drop table stock
#%sql drop table germplasm
#%sql drop table gene
%sql CREATE TABLE stock(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, amount FLOAT NOT NULL, date DATE NOT NULL, location VARCHAR(20) NOT NULL);
%sql DESCRIBE stock
%sql CREATE TABLE germplasm(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, taxonid INTEGER NOT NULL, alleles VARCHAR(30) NOT NULL, stock_id INTEGER NOT NULL);
%sql DESCRIBE germplasm
%sql CREATE TABLE gene(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, gene VARCHAR(10) NOT NULL, gene_name VARCHAR(30) NOT NULL, germ_id INTEGER NOT NULL, embl VARCHAR(70) NOT NULL);
%sql DESCRIBE gene


%sql INSERT INTO stock(amount, date, location) VALUES (5, '2013-05-10', 'Room 2234');
%sql INSERT INTO stock(amount, date, location) VALUES (9.8, '2015-1-12', 'Room 998');

%sql INSERT INTO germplasm (taxonid, alleles, stock_id) VALUES (4150, 'def-1', 1 );
%sql INSERT INTO germplasm (taxonid, alleles, stock_id) VALUES (3701, 'ap3 ag', 2 );

%sql INSERT INTO gene (gene, gene_name, germ_id, embl) VALUES ('DEF', "Deficiens", 1, 'https://www.ebi.ac.uk/ena/data/view/AB516402');
%sql INSERT INTO gene (gene, gene_name, germ_id, embl) VALUES ('AP3', "Apetala3", 2, 'https://www.ebi.ac.uk/ena/data/view/AF056541');
%sql INSERT INTO gene (gene, gene_name, germ_id, embl) VALUES ('AG', "Agamous", 2, 'https://www.ebi.ac.uk/ena/data/view/AL161549');<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
1 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
1 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
1 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
1 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
1 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
1 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
1 rows affected.<jupyter_text>Our germplasm database is now set up.**This data structure is not the same as our previous examples!**Now we allow a germplasm to be a 'double mutant' (or a 'triple mutant'), so some germplasms connect to multiple genes; the way I modelled this situation is to **point from the gene table to the ID of the germplasm table** (in our previous table structure, the germplasm table contained the ID of the gene)**because it is many to one, you point from each of the many to the one****Germplasm table** linked to **Stock table** (one-to-one) (one germplasm point to one stock)**Gene table** linked to **Germplasm table** (many-to-one) (many genes may be involved in one germplasm)<jupyter_code># instead of connecting via sqlMagic, we are now connecting via pymysql

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='germplasm',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit = True)

#connection.autocommit(True) 
# note that it is possible to delay putting changes into the database!

try: # try: except: finally: try this, if something goes wrong, it can do excepts, and at the end it always does finally
    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT * FROM stock"
        cursor.execute(sql)
        results = cursor.fetchall() # fetch all the responses, results is a list
        print(results)
        print("")
        for result in results:
            print(result['amount']," is located in ",result['location'])
finally:
    print("")
    #connection.close()   

    ## in real software, we would like to close the connection as part of our finally
    ## in this case it is commented out because we are going to reuse that connection object everywhere else
    
###### IF WE CLOSE THE CONNECTION, WE'RE GOING TO HAVE TO RUN THE CONNECTION THING FROM ABOVE AGAIN!<jupyter_output>[{'id': 1, 'amount': 5.0, 'date': datetime.date(2013, 5, 10), 'location': 'Room 2234'}, {'id': 2, 'amount': 9.8, 'date': datetime.date(2015, 1, 12), 'location': 'Room 998'}]

5.0  is located in  Room 2234
9.8  is located in  Room 998<jupyter_text>general structuretry:    with connection.cursor() as cursor:        sql =  sql command between ""        cursor.execute(sql)  executing the sql command        results = cursor.fetchall()  fetch all the responses         then whatever        finally:    print("")    connection.close()<jupyter_code>try:
    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT * FROM gene"
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
        print("")
        for result in results:
            print(result['gene_name'])
finally:
    print("")
    #connection.close()<jupyter_output>[{'id': 1, 'gene': 'DEF', 'gene_name': 'Deficiens', 'germ_id': 1, 'embl': 'https://www.ebi.ac.uk/ena/data/view/AB516402'}, {'id': 2, 'gene': 'AP3', 'gene_name': 'Apetala3', 'germ_id': 2, 'embl': 'https://www.ebi.ac.uk/ena/data/view/AF056541'}, {'id': 3, 'gene': 'AG', 'gene_name': 'Agamous', 'germ_id': 2, 'embl': 'https://www.ebi.ac.uk/ena/data/view/AL161549'}]

Deficiens
Apetala3
Agamous<jupyter_text>Open a new jupyter notebook to Lesson 3  - mySQL sectionTry some of the SELECT queries we learned in that lesson.<jupyter_code># try queries here

try:
    with connection.cursor() as cursor:
        # Read a single record
        sql = """SELECT * FROM germplasm, stock, gene WHERE 
     germplasm.stock_id = stock.id AND 
     gene.germ_id = germplasm.id;""" # we use multiline structure!!
        cursor.execute(sql)
        results = cursor.fetchall()  # traertodos()
        #print(results)
        #print("")
        for result in results:
            print(result)
            print()
finally:
    print("")
    #connection.close()<jupyter_output>{'id': 1, 'taxonid': 4150, 'alleles': 'def-1', 'stock_id': 1, 'stock.id': 1, 'amount': 5.0, 'date': datetime.date(2013, 5, 10), 'location': 'Room 2234', 'gene.id': 1, 'gene': 'DEF', 'gene_name': 'Deficiens', 'germ_id': 1, 'embl': 'https://www.ebi.ac.uk/ena/data/view/AB516402'}

{'id': 2, 'taxonid': 3701, 'alleles': 'ap3 ag', 'stock_id': 2, 'stock.id': 2, 'amount': 9.8, 'date': datetime.date(2015, 1, 12), 'location': 'Room 998', 'gene.id': 2, 'gene': 'AP3', 'gene_name': 'Apetala3', 'germ_id': 2, 'embl': 'https://www.ebi.ac.uk/ena/data/view/AF056541'}

{'id': 2, 'taxonid': 3701, 'alleles': 'ap3 ag', 'stock_id': 2, 'stock.id': 2, 'amount': 9.8, 'date': datetime.date(2015, 1, 12), 'location': 'Room 998', 'gene.id': 3, 'gene': 'AG', 'gene_name': 'Agamous', 'germ_id': 2, 'embl': 'https://www.ebi.ac.uk/ena/data/view/AL161549'}<jupyter_text>notice that in the results, the germplasm.id comes out as id (in the first word of each thing) but for example stock.id comes as stock.idyou can select specific columns (germplasm.alleles for example) and it will come out as alleles without germplasm.if the name is ambiguous and exists in many tables, you have to prefixwhen it comes out the key is only alleles. SELECT whatever AS name FROM blabla... -> this way we get a distinct keyname (name)it doesn't change the result, just the name of the key of the result dictionary fetchall() always returns a list  You can issue ANY mysql command in this wayIncluding _create database_, _create table_,  and  _insert_ data commands.For example:<jupyter_code>connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='germplasm',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit = True)

try:
    with connection.cursor() as cursor:
        sql = "create database testing123"
        cursor.execute(sql)

finally:
    print("")
    #connection.close()
    

%sql show databases

#%sql drop database testing123
%sql show databases

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='germplasm',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit = True)

try:
    with connection.cursor() as cursor:
        sql = "show databases"
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
        for result in results:
            print(result['Database'])
        print("")

finally:
    print("")
    connection.close()




connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
# PAY ATTENTION HERE!!!!!!!!!!!!!!!
                             db='testing123', ##### we are recreating our connection object with the NEW DB
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit = True)


try:
    with connection.cursor() as cursor:
        sql = "create table test1(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, data1 VARCHAR(20) NOT NULL )"
        cursor.execute(sql)
        sql = "create table test2(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, otherdata VARCHAR(20) NOT NULL )"
        cursor.execute(sql)
        
        #connection.commit()
finally:
    print("")
    connection.close()


%sql use testing123
#%sql show tables
#%sql desc test1
%sql desc test2 # desc is describe, it will show the field definitions for anything in that table<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
0 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
2 rows affected.<jupyter_text>desc tablename --> shows the field definitions for every field in that table<jupyter_code>%sql show databases<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
6 rows affected.<jupyter_text>Insert a new record into the Germplasm databaseFirst, look at the schemas:<jupyter_code>%sql use germplasm
%sql desc gene
%sql desc stock
%sql desc germplasm
%sql SELECT * FROM stock<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
2 rows affected.<jupyter_text>test data to show how to retrieve the latest unique ID number:**Stock**:  amount=5, date=2019-12-12, location=nowhere(Remember the SQL "last_insert_id()" function!!)  REMEMBER: RECONNECT TO THE CORRECT DATABASE WHEN USING MORE THAN ONE!! FOR THE EXAM<jupyter_code>connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
# PAY ATTENTION HERE!!!!!!!!!!!!!!!
                             db='germplasm',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit = True)

try:
    with connection.cursor() as cursor:
        sql = """INSERT INTO stock (amount, date, location)
        VALUES (5, '2019-12-12', 'nowhere')"""
        cursor.execute(sql)
        sql = "SELECT last_insert_id()"
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results) # the result object is always a list
        # note that results is a LIST, 
        # so we need to take element 0 then
        # gene_id = results[0]['last_insert_id()']
        print("The unique ID for the last gene entered was {}".format(results[0]['last_insert_id()']))
        stockid = results[0]['last_insert_id()']
        print("STOCK ", stockid)
        
        #connection.commit()

finally:
    print("")
    connection.close()

%sql SELECT * FROM stock<jupyter_output>[{'last_insert_id()': 4}]
The unique ID for the last gene entered was 4
STOCK  4

 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
4 rows affected.<jupyter_text>results has [{'last_insert_id()': 3}]results[0] has {'last_insert_id()': 3}results[0]['last_insert_id()'] has 3  new data:**Gene**:  gene=WUS, gene_name=WUSCHEL, embl=http://ABC123, **germ_id=??****Gene**:  gene=CLV, gene_name=CLAVATA, embl=http://ABC123, **germ_id=??****Stock**: amount=10, date=12/09/2018, location=Room990**Germplasm**:  taxonid=3701, alleles= wus-1 clv-1, stock_id=??**<jupyter_code>connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='germplasm',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit = True)

try:
    with connection.cursor() as cursor:
        
        sql = """INSERT INTO stock (amount, date, location) 
        VALUES (10, '2018-12-09', 'Room990')"""
        cursor.execute(sql)
        sql = "SELECT last_insert_id()"
        cursor.execute(sql)
        results = cursor.fetchall()   # traertodos
        stockid = results[0]['last_insert_id()']
        print(stockid)
        
        ## ^ you insert a new stock row 
        ## and then store its id as stockid

        sql = "INSERT INTO germplasm (taxonid, alleles, stock_id) VALUES (3701, 'wus-1 clv-1', " + str(stockid) + ")"
        # you are concatenating the strings!! the sql command has to be a string
        cursor.execute(sql)
        sql = "SELECT last_insert_id()"
        cursor.execute(sql)
        results = cursor.fetchall()  # traertodos
        germid = results[0]['last_insert_id()']
        print(germid)
        
        ## ^ you insert a new germplasm row and use stockid to synchronise it with the above table
        ## and then store its id as germid

        sql = """INSERT INTO gene (gene, gene_name, embl, germ_id) 
        VALUES ("WUS", 'WUSCHEL', 'http://blahblah1', """ + str(germid) + """)"""
        cursor.execute(sql)
        
        ## ^you insert a new gene row and use germid to synchronise it with the above table
        ## you dont store any id here because it is the last thing
        ## (THINK ABOUT THE DATABASE STRUCTURE)
        
        sql = """INSERT INTO gene (gene, gene_name, embl, germ_id) 
        VALUES ("CLV", 'CLAVATA', 'http://blahblah2', """ + str(germid) + """)"""
        cursor.execute(sql)
        
        # idem, in this case this relationship is one to many, so the many point to the one, that is why
        # gene has germid and not viceversa, and why two genes have the same germid
        # (because the same germplasm can have many genes)

        #connection.commit()   # NOTE THAT I AM FORCING THE WRITE TO THE DATABASE HERE
        
finally:
    print("")
    connection.close()  # if I close before I commit, the inserts are lost

    
%sql select * from gene;
%sql use germplasm
%sql select * from germplasm;<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
0 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
3 rows affected.
{'fraction_non_alphanumeric': 0.1254355400696864, 'fraction_numerical': 0.03099211443242252, 'mean_word_length': 2.871273071462376, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 37, 'https://': 9, 'lorem ipsum': 0, 'www.': 9, 'xml': 0}, 'pii_count': 15, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '280203', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 5476, 'n_tokens_neox': 5034, 'n_words': 1640, 'path': 'Lesson 5c - Accessing mySQL from Python.ipynb', 'repo_name': 'javierlopezrodriguez/Accelerated_Intro_to_CompBio_Part_2', 'size': '16328'}