# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ## Using SQL Databases in Python
#
# You 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.
#
#

# %load_ext sql
# #%config SqlMagic.autocommit=False
# %sql mysql+pymysql://root:root@127.0.0.1:3306/mysql

# %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 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, allele VARCHAR(10) NOT NULL, gene_id INTEGER 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, embl VARCHAR(70) NOT NULL);
# %sql DESCRIBE gene



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

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

# %sql INSERT INTO gene (id, gene, gene_name, embl) VALUES (1, 'DEF', "Deficiens", 'https://www.ebi.ac.uk/ena/data/view/AB516402');
# %sql INSERT INTO gene (id, gene, gene_name, embl) VALUES (2, 'AP3', "Apetala3", 'https://www.ebi.ac.uk/ena/data/view/AF056541');
# %sql INSERT INTO gene (id, gene, gene_name, embl) VALUES (3, 'AG', "Agamous", 'https://www.ebi.ac.uk/ena/data/view/AL161549');


# -

# <pre>
#
#
# </pre>
#
# ## Our germplasm database is now set up.
#
# The data structure is approximately the same as the final data structure yesterday:
#
# **Stock table**
#
# **Gene table**
#
# **Germplasm table** linked to **Stock table** (many-to-one) (several germplasms point to same stock)
# **Germplasm table** linked to **Gene table** (one-to-one) (every germplasm points to one gene)
#

# +
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)

try:
    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT * FROM stock"
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
        print("")
        for result in results:
            print(result['amount']," is located in ",result['location'])
finally:
    print("")
    connection.close()
# -

# ## Open a new jupyter notebook to Lesson 3  - mySQL section
#
# Try some of the SELECT queries we learned in that lesson.  

# +
# try queries here
# -

# <pre>
#
#
# </pre>
# ## You can issue ANY mysql command in this way
#
# Including _create database_, _create table_,  and  _insert_ data commands.
#
# For example:

# +
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)

try:
    with connection.cursor() as cursor:
        # Read a single record
        sql = "create database testing123"
        cursor.execute(sql)
finally:
    print("")
    connection.close()


# %sql show databases


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

try:
    with connection.cursor() as cursor:
        # Read a single record
        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)
finally:
    print("")
    connection.close()


# %sql use testing123
# #%sql show tables
# #%sql desc test1
# %sql desc test2

# -

# %sql show databases

# # Insert a new record into the Germplasm database
#
# First, look at the schemas:
#

# %sql use germplasm
# %sql desc gene

# %sql desc stock

# %sql desc germplasm

# ## test data to show how to retrieve the latest unique ID number:
#
# **Gene**:  gene=TEST, gene_name=testing, embl=http://TESTTEST
#
# (Remember the SQL "last_insert_id()" function!!)
#

# +
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)

try:
    with connection.cursor() as cursor:
        # Read a single record
        sql = "INSERT INTO gene (gene, gene_name, embl) VALUES ('TEST', 'testing', 'https://TESTTEST')"
        cursor.execute(sql)
        sql = "SELECT last_insert_id()"
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
        gene_id = results[0]['last_insert_id()']# note that results is a LIST, so we need to take element 0 first
        print("The unique ID for the last gene entered was {}".format(gene_id))

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


# -

# ## new data:
#
# **Gene**:  gene=WUS, gene_name=wuschel, embl=http://ABC123
#
# **Stock**: amount=10, date=12/09/2018, location=Room990
#
# **Germplasm**:  taxonid=3701, allele=wus-1, **gene_id=??, stock_id=??**
#
#

# +
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)

try:
    with connection.cursor() as cursor:
        
        # you will need to create 3 INSERT INTO statements
        # you will need to capture the unique ID from two 
        # of those to create the germplasm entry
        sql = ""

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



StarcoderdataJupyterScriptsDedupFiltered