4 Connecting to a Database

Contents

The first step to reading or writing to a database is the connection.

For each language, we go through code to ask the user to input the address, database name, username, and password each time the code is run. Asking the user to input credentials is good practice for scenarios when you’re sharing code, working on a shared computer, posting code publically on GitHub or elsewhere, or using sensitive data. However, if the files you’re working with are private and you feel comfortable, you can replace these with the raw strings of your credentials (e.g. database = "database name").

4.1 Julia

using ODBC

# Get user credentials
address = begin 
    print("Enter Address: ")
    readline()
end 

database = begin 
    print("Enter Database Name: ")
    readline()
end 

user = begin 
    print("Enter Username: ")
    readline() 
end 

crypt = Base.getpass("Enter Password: ")
pass = read(crypt,String)

# setting up database  
dsn = ODBC.DSN("Driver={ODBC Driver 17 for SQL Server};Address=$address;Database=$database;UID=$user;PWD=$pass;")

# disconnect when you're done using this connection
ODBC.disconnect()

4.2 SAS

When using SAS, we can connect through proc sql or using a libname. We would recommend the latter, as the connection can be referenced throughout your sql file, as opposed to within one proc sql statement.

Proc Sql

/*from proc sql directly*/
proc sql;
connect to odbc as conn
required="Driver={ODBC Driver 17 for SQL Server};Address=<address>;Database=<database name>;UID=<username>;PWD=<password>";

create table event as
select * from connection to conn
(select * from events) ;

/*disconnect when you're done using this connection*/
disconnect from conn;
quit;

Libname

/*by using a libname*/
libname conn2 odbc
required ="Driver={ODBC Driver 17 for SQL Server};Address=<address>;Database=<database name>;UID=<username>;PWD=<password>";

proc sql;
create table event as
select * from conn2.events ;

/*disconnect when you're done using this connection*/
disconnect from conn2;
quit;

User input for credentials

Here we are going to assign a new libname in order to demonstrate the password protective features of SAS. Running this code chunk will prompt the user to input their credentials, then create a connection with the inputted id and password.

/** %WINDOW defines the prompt **/
%window info
  #5 @5 'Please enter userid:'
  #5 @26 id 8 attr=underline
  #7 @5 'Please enter password:'
  #7 @28 pass 8 attr=underline display=no;

/** %DISPLAY invokes the prompt **/
%display info;

libname conn2 odbc
  required ="Driver={ODBC Driver 17 for SQL Server};Address=<address>;Database=<database name>;UID=&id;PWD=&pass"
  bulkload = YES
;

The bulkload option can be removed if you don’t have permissions. It will be a bit slower, but will work the same.

4.3 R

Using R, we connect to the database using the odbc and DBI packages.

library(odbc)
library(DBI)

# prompt user input for credentials
address = rstudioapi::showPrompt("Address","Address")
database = rstudioapi::showPrompt("Database name","Database name")
uid = rstudioapi::showPrompt("Database username", "Database username")
pwd = rstudioapi::askForPassword("Database password")

# open connection
conn <- dbConnect(
  odbc(),
  Driver = "ODBC Driver 17 for SQL Server",
  Server = address,
  Database = database,
  UID = uid,
  PWD = pwd
)

# disconnect when you're done using this connection
dbDisconnect(conn)

4.4 Python

The driver used in Python may depend on your setup. If you’re working on a PC, downloading the driver and connection from Microsoft directly should allow you to reference the driver name like this:

driver = "{ODBC Driver 17 for SQL Server}"

If you’re working on a Mac or Linux device, you cannot do this without further setup. It’s easiest to pass in the path to the installed .dylib file, which should be installed to /usr/local/lib by default. With this setup, you will need to reference the driver like this:

driver = "/usr/local/lib/libmsodbcsql.17.dylib"

If copying and pasting the code below, switch which driver = line is commented depending on your operating system.

import pyodbc

driver = "{ODBC Driver 17 for SQL Server}" # PC Users
# driver = "/usr/local/lib/libmsodbcsql.17.dylib" # Mac Users

# prompt user input for credentials
address = input("Address: ")
database = input("Database name: ")
username = input("Username: ")
password = getpass.getpass(prompt = "Password: ")

# open connection
conn = pyodbc.connect(
    ";".join([
        "Driver="+driver,
        "Address="+address,
        "Database="+database,
        "UID="+username,
        "PWD="+password
    ])
)

# get cursor
cursor = conn.cursor()

# disconnect when you're done using this connection
conn.close()