.. _SQL3:

******************
SQL3
******************

Lab Materials
=============

This lab will use the following data:

**Riders**

    +-----------+----------+----------+
    | Name      | Age      | Gender   |
    +===========+==========+==========+
    | Lindsay   | 16       | Female   |
    +-----------+----------+----------+
    | Tom       | 21       | Male     |
    +-----------+----------+----------+
    | Eric      | 33       | Male     |
    +-----------+----------+----------+
    | Marco     | 13       | Male     |
    +-----------+----------+----------+
    | Kristopher| 45       | Male     |
    +-----------+----------+----------+
    | Kelly     | 21       | Female   |
    +-----------+----------+----------+
    | Mitchell  | 24       | Male     |
    +-----------+----------+----------+
    | Tammy     | 30       | Female   |
    +-----------+----------+----------+
    | Brad      | 18       | Male     |
    +-----------+----------+----------+

**Make**

    +-----------+----------+
    | Name      | Make     |
    +===========+==========+
    | Lindsay   | Honda    |
    +-----------+----------+
    | Lindsay   | Yamaha   |
    +-----------+----------+
    | Tom       | Honda    |
    +-----------+----------+
    | Eric      | Honda    |
    +-----------+----------+
    | Eric      | Yamaha   |
    +-----------+----------+
    | Marco     | Honda    |
    +-----------+----------+
    | Marco     | Yamaha   |
    +-----------+----------+
    | Marco     | Kawasaki |
    +-----------+----------+
    | Marco     | Suzuki   |
    +-----------+----------+
    | Marco     | KTM      |
    +-----------+----------+
    | Mitchell  | Honda    |
    +-----------+----------+
    | Mitchell  | Yamaha   |
    +-----------+----------+
    | Kelly     | Honda    |
    +-----------+----------+
    | Kristopher| Honda    |
    +-----------+----------+
    | Kristopher| Yamaha   |
    +-----------+----------+
    | Kristopher| Kawasaki |
    +-----------+----------+
    | Tammy     | Honda    |
    +-----------+----------+
    | Tammy     | Yamaha   |
    +-----------+----------+
    | Brad      | Honda    |
    +-----------+----------+
    | Brad      | Yamaha   |
    +-----------+----------+

**Track**

    +-------------------+----------+----------+
    | Track             | Type     | Bike     |
    +===================+==========+==========+
    | Carters Racetrack | dirt     | Honda    |
    +-------------------+----------+----------+
    | Carters Racetrack | dirt     | Yamaha   |
    +-------------------+----------+----------+
    | RaceTrack Empire  | asphalt  | Honda    |
    +-------------------+----------+----------+
    | RaceTrack Empire  | asphalt  | Kawasaki |
    +-------------------+----------+----------+
    | Dirt Roads        | dirt     | Honda    |
    +-------------------+----------+----------+
    | Dirt Roads        | dirt     | Kawasaki |
    +-------------------+----------+----------+
    | Dirt Roads        | dirt     | KTM      |
    +-------------------+----------+----------+
    | Dirt Roads        | dirt     | Suzuki   |
    +-------------------+----------+----------+
    | Speedy Pavement   | asphalt  | Honda    |
    +-------------------+----------+----------+
    | Speedy Pavement   | asphalt  | KTM      |
    +-------------------+----------+----------+
    | Speedy Pavement   | asphalt  | Yamaha   |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | Honda    |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | Yamaha   |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | Kawasaki |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | KTM      |
    +-------------------+----------+----------+
    | Expressways       | asphalt  | Suzuki   |
    +-------------------+----------+----------+
    | Georgia ATV Trails| dirt     | Honda    |
    +-------------------+----------+----------+
    | Georgia ATV Trails| dirt     | KTM      |
    +-------------------+----------+----------+
    | Georgia ATV Trails| dirt     | Suzuki   |
    +-------------------+----------+----------+

Lab Content
===========

Objective
---------

Procedure
---------

Creating a CSV file using Python
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can automatically generate a csv file Python as follows::

    import csv

    data = [
    ['Name', 'Age', 'Gender'],
    ['Lindsay',16,'female'],
    ['Tom',21, 'male'],
    ['Eric', 33, 'male'],
    ['Marco', 13, 'male'],
    ['Kristoper', 45,'male'],
    ['Kelly', 21, 'female'],
    ['Mitchell', 24, 'male'],
    ['Tammy', 30, 'female'],
    ['Brad', 30, 'male']
        ]

    c = csv.writer(open('C:\Users\crlyli0476\sample.csv', 'wb'))
    for row in data:
        c.writerow(row)


Importing a CSV file into PgAdminIII using Python
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can import CSV files into the database using Python. First, we need to create a table to hold the data using Python
   as follows::

    cr = csv.reader(open('C:\Users\crlyli0476\sample.csv', "rb"))
    cur.execute("""CREATE TABLE example(
        name varchar,
        age integer,
        gender varchar)
               """)
    conn.commit()

#. Now we can import the CSV data using Python as follows::

       for row in cr:
            if row[0] == 'Name':
                pass
            else:
                cur.execute("""INSERT INTO example VALUES (%s, %s, %s);""", (row[0], row[1], row[2]))

       conn.commit()


#. Create a CSV file for the Make and Track tables and import the data into your database.

Exporting data as a CSV file using Python
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can export the data from the database to a CSV file using Python as follows::

    cur.execute("""SELECT * FROM example""")

    csv_writer = csv.writer(open('C:\\Users\\crlyli0476\\exampleEx.csv', 'wt'), lineterminator='\n')
    csv_writer.writerow([i[0] for i in cur.description]) #writes headers
    csv_writer.writerows(cur.fetchall())
    del csv_writer


Importing a CSV file using PSQL Console
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. Create a CSV file in Microsoft Excel with the name, age and gender columns and populate the rows with the data
   provided in the table Riders. Name your file RiderImport.csv and save it to your C: drive.
   Note: The file can be saved in any directory, but saving it to the C: drive will shorten your path.

#. Open PgAdminIII and log into your database. At the top of the window, you will see a tab titled Plugins. Select
   PSQL Console. Create a table titled riders with the appropriate columns.
   Notice: If you refresh your tables tab in the Object browser, your new table has been created. This is shown in the
   second image below.

.. figure:: /_static/hws/SQL3/SQL3_1.PNG
    :align: center
    :scale: 75%


.. figure:: /_static/hws/SQL3/SQL3_2.PNG
    :align: center
    :scale: 75%

#. We can import the RiderImport.csv we created earlier using the PSQL Console using the following command
   \COPY <table> FROM '[PATH]:\filename.csv' CSV HEADER; An example is shown in the image below.

.. figure:: /_static/hws/SQL3/SQL3_3.PNG
    :align: center
    :scale: 75%


Exporting data as a CSV file using PSQL Console
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#. We can also export the data in our tables to a CSV file. Open the PSQL Console or continue using the one
   you have open. Use the following command. \COPY <table> TO '[PATH]:\newFilename.csv' CSV HEADER;

.. figure:: /_static/hws/SQL3/SQL3_4.PNG
    :align: center
    :scale: 75%





References
----------
Update soon.


Metadata
========
| Title:SQL3
| CreationDate:03/05/2014
| SoftwareUsed:PostgresSQL
| SoftwareVersion: 9.1
| Contributors: Zac Miller, Crystal Lyliston