<jupyter_start><jupyter_text>Accessing the SCP York databaseA quick demonstration on how to do this using the R psotgres library`install.packages('RPostgreSQL')`For questions contact <EMAIL>]R can be installed on anaconda using `conda install -c r r-irkernel`<jupyter_code>library('RPostgreSQL')
# Lets log into the database

pg = dbDriver("PostgreSQL")

cred = scan('~/.postgres_login', what="character")

con = dbConnect(pg, user= cred[1], password= cred[2],
                host= paste(cred[3],".york.ac.uk",sep=''),
                port=5432, dbname=cred[4])<jupyter_output><empty_output><jupyter_text>Now we are connected, we can have a look at querying.  Reading a TableLets read the first 5 results from the countries table.Double quotation marks are not usually needed.<jupyter_code>sql = 'SELECT * from "comm_trade.countries" LIMIT 5;'
# read back the full table
dtab = dbGetQuery(con, sql)
dtab<jupyter_output><empty_output><jupyter_text>If we wish to only take the 'name' and 'area_code' columns:<jupyter_code>sql = '
        SELECT name,area_code 
        from "comm_trade.countries" 
        LIMIT 5;
'
# read back the full table
dtab = dbGetQuery(con, sql)
dtab<jupyter_output><empty_output><jupyter_text>StructureDatabases consist of groups of tables (Schemas). These represent the different projects that may exist List all schemasUse `\dn+` in psql<jupyter_code>sql = 'SELECT schema_name
       FROM information_schema.schemata'
dbGetQuery(con, sql)<jupyter_output><empty_output><jupyter_text>If we then wish to see what tables exist within a certain schema (eg. fao)<jupyter_code>sql = "
        SELECT table_name 
        FROM information_schema.tables
        WHERE table_schema='fao';
"
dtab = dbGetQuery(con, sql)
dtab<jupyter_output><empty_output><jupyter_text>Finally we may also view the properties of a table within that schema, e.g. the commodity balance table from the fao schemaUse `\dt` tablename in psql<jupyter_code>sql = "
    SELECT table_catalog,table_schema,table_name,column_name,ordinal_position,is_nullable,data_type,character_maximum_length
    FROM information_schema.columns 
    WHERE table_schema = 'fao' AND table_name = 'cb_production'
;"
dtab = dbGetQuery(con, sql)
dtab<jupyter_output><empty_output><jupyter_text>Querying  Now lets explore the contents(showing only the first 5 results)<jupyter_code>sql = 'SELECT * from fao.cb_production LIMIT 5;'
dtab = dbGetQuery(con, sql)
dtab<jupyter_output><empty_output><jupyter_text>What if we want only the values for year 2013<jupyter_code>sql = 'SELECT * from fao.cb_production WHERE year = 2013 LIMIT 5;'
dbGetQuery(con, sql)<jupyter_output><empty_output><jupyter_text>Asigning an alias in the queryWe may wish to shorten the name of what we are querying to save typing later on. Here we give the commodity balance production table the name `p`.<jupyter_code>sql = '
    SELECT * 
    FROM fao.cb_production p 
    WHERE p.year = 2013 
    LIMIT 5;'
dbGetQuery(con, sql)<jupyter_output><empty_output><jupyter_text>Link two tablesSometimes we wish to combine two tables to produce an output. We can do this through one of the many possible join commands. Here we join our item table with the current one on the item_code columns (conveniently named the same in both tables) using `JOIN items i ON(p.item_code = i.item_code)`<jupyter_code>sql = '
    SELECT * 
    FROM fao.cb_production p 
    JOIN fao.items i ON(p.item_code = i.item_code) 
    WHERE p.year=2013  
    LIMIT 10;'
dbGetQuery(con, sql)<jupyter_output><empty_output><jupyter_text>Unique (DISTINCT) valuesIf we dont want duplicates, we can always call upon the distinct selector.<jupyter_code># Get all the years in the year column, and return a list with no duplicates
sql = 'SELECT DISTINCT year 
       FROM fao.cb_production 
       ORDER BY "year" DESC
       LIMIT 5
'
dbGetQuery(con, sql)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08526532715095311, 'fraction_numerical': 0.008758371973209686, 'mean_word_length': 4.955521472392638, 'pattern_counts': {'":': 0, '<': 46, '<?xml version=': 0, '>': 46, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '586657', 'license': 'MIT', 'n_tokens_mistral': 1312, 'n_tokens_neox': 1247, 'n_words': 397, 'path': 'R_postgres_demo.ipynb', 'repo_name': 'scpyork/postgresql', 'size': '3899'}