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

1. 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

1. 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()
   

2. 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()
   

3. 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

1. 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

1. 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.
2. 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.

1. 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.

Exporting data as a CSV file using PSQL Console

1. 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;

References

Update soon.

Metadata

Title:SQL3

CreationDate:03/05/2014

SoftwareUsed:PostgresSQL

SoftwareVersion: 9.1

Contributors: Zac Miller, Crystal Lyliston