-
Basic Functionality 6
-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
Pandas & Indices 4
-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
-
Joins, Unions & Order By 3
-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
-
Group By & Views 3
-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Update
By using ALTER TABLE, and ADD COLUMN, we can create new columns within a table. The following will create an ID column which takes integer values.
#We can add a column by using alter
query = """ALTER TABLE students
ADD COLUMN ID int;"""
cur.execute(query)
When we add this new column, the values are all null.
for row in cur.execute('SELECT * FROM students;'):
print(row)
If we are using UPDATE and SET, we are able to change column values. The following code will take the ID variable and set it equal to 1.
#You can give values with update
query = """UPDATE students SET ID = 1;"""
cur.execute(query)
for row in cur.execute('SELECT * FROM students;'):
print(row)
Like we did with SELECT, we can use conditions. This will let us set variables based upon a condition. Below, we set the ID equal to 5 for entries which have grades less than 92.
#You can add in a clause
query = """UPDATE students SET ID = 5 WHERE grade <92;"""
cur.execute(query)
for row in cur.execute('SELECT * FROM students;'):
print(row)
Another way to quickly get all the data is to use fetchall() on the cursor. This returns as a list all the entries.
#Using fetchall is a quick way to grab all results
cur.execute('SELECT * FROM students;')
data = cur.fetchall()
print(data)