# import
import sqlite3
import pandas as pd
# connect database
db = sqlite3.connect('Canidae_COI.db')
def get_data(query):
'''
get data from the database
'''
return pd.read_sql(query, db)
cursor = db.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tableNames = cursor.fetchall()
tableNames = [ tableNames[i][0] for i in range(len(tableNames)) ]
print(tableNames)
query = 'SELECT * FROM species;'
speciesDF = get_data(query)
speciesDF
speciesSmallerDF = get_data('SELECT recordid, species_name, subspecies_name FROM species LIMIT 100;')
speciesSmallerDF
speciesOrderSmallerDF = get_data('SELECT recordid, species_name, subspecies_name FROM species ORDER BY species_name DESC LIMIT 100;')
speciesOrderSmallerDF
query = '''
SELECT recordid, species_name, subspecies_name
FROM species
WHERE ( subspecies_name != "None" )
ORDER BY subspecies_name
'''
speciesWithSubSpeciesDF = get_data(query)
speciesWithSubSpeciesDF
query = '''
SELECT recordid, species_name, subspecies_name
FROM species
WHERE ( subspecies_name != "None" AND species_name == "Canis lupus")
ORDER BY subspecies_name
'''
speciesWithSubSpeciesDF = get_data(query)
speciesWithSubSpeciesDF
howManyCanisLupus = get_data('SELECT COUNT(*) FROM species WHERE species_name == "Canis lupus";')
howManyCanisLupus
query = '''
SELECT species_name, COUNT(*)
FROM species
WHERE genus_name == "Canis"
GROUP BY species_name;
'''
howManyPerGenus = get_data(query)
howManyPerGenus
get_data('SELECT AVG(recordid), MIN(recordid), MAX(recordid) FROM species WHERE species_name == "Canis adustus";')
query = '''
SELECT species.recordid AS "RecordID", species.species_name AS "Species Name", bins.bin_uri AS "BIN"
FROM species INNER JOIN bins
ON species.recordid == bins.recordid
where species_name != "None"
ORDER BY bin_uri DESC
'''
allDataDF = get_data(query)
allDataDF
query = '''
SELECT species.species_name AS Species, bins.bin_uri AS BIN, COUNT(*) AS Count
FROM species INNER JOIN bins
ON species.recordid == bins.recordid
WHERE species.genus_name == "Vulpes"
GROUP BY BIN
ORDER BY Species ASC, Count DESC
;
'''
get_data(query)