Preview

4 - Full Stack - connect to a SQL database

 1. Our flask application uses a SQLite database to store __________. Python comes with built-in support for SQLite in the sqlite3 module. 
Note: Please also refer to our SQL and Database series

  user data and comments

  python commands

  database queries

  sql database connections

 2. SQL is a standard language for storing, manipulating and retrieving data in databases. Insert the missing statement to get all the columns from the Users table. 
_______
 * FROM Users;

  SELECT

  GET

  POST

  FIND

 3. Assuming the Users table in the database also has a field called City, fill in the blanks to select the city from the User's table. 
______________ Users;

  SELECT City FROM

  FIND City FROM

  GET City in

  SELECT City IN

 4. The following code contained in the db-create.py file creates ____________________. 
import sqlite3
db_locale='users.db'

connie=sqlite3.connect(db_locale)
c=connie.cursor() #use this to create commands

#creating a multi-line instruction
c.execute("""
CREATE TABLE comments
(id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT,
	title TEXT,
	comment TEXT,
	date_posted TEXT
)
	""")

connie.commit()
connie.close()

  a table called Comments with two fields: one Autoincrement and one TEXT

  a file called Comments with a single field

  a table called db_locale with five tables

  a table called Comments with five fields.

 5. The following code creates a query that selects all the data from the table and puts it into a variable. In order for it to be executed, can you spot what is missing? 
#populate database file
import sqlite3
db_locale='users.db'

connie = sqlite3.connect(db_locale)
c = connie.cursor() #use this to create commands

#creating a multi-line instruction
c.execute("""

SELECT * FROM comments

	""")

user_info=c.fetchall() #can do fetchone or fetchall -can also specify which one etc
print(user_info)

print()

for user in user_info:
	print(user)

  The lines connie.commit() connie.close() should go at the end

  The line import sql_commands at the top

  Nothing is missing -the code will execute and the query will work

  The lines db_locale.commit() and db_locale.close() at the end

 6. The ___________________ statement copies data from one table and inserts it into another table.

  POST INTO

  INSERT INTO SELECT

  SELECT INTO

  SELECT FROM INTO

 7. The following is a function that includes a query command. To put the results of this query into another variable called user_data, we would use the command: 
def query_comments():
        connie = sqlite3.connect(db_locale)
        c=connie.cursor()
        c.execute("""
        SELECT * FROM comments	

		""")
        userdata=c.fetchall()
        return userdata

  user_data=userdata

  user_data=query_comments()

  user_data=c.fetchall()

  query_comments=user_data

 8. Assuming user_data has been passed to the html page, what is the correct syntax of the opening for loop (jinja)? 
{?????WHAT GOES HERE???????}
    <tr>
      <th scope="row">{{user[0]}}</th>
      <td>{{user[2]}}</td>
      <td>{{user[1]}}</td>
      <td>{{user[3]}}</td>
    </tr>
	{% endfor %}

  {% for user_data in users %}

  {% for 0-5 in user_data%}

  {% user_data for all%}

  {% for user in user_data%}

 9. The following SQL creates a ________ on the "ID" column when the "Persons" table is created. A _______ must always be unique and cannot be NULL. 
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

  PRIMARY KEY

  FIRST FIELD

  SECONDARY KEY

  REPEATED ID FIELD

 10. SQL question: Assuming you had a table called Users with countries stored. Fill in the blanks to isolate ALL users from the country=United Kingdom. 
SELECT * FROM Users
_____________________________.

  WHERE Country='United Kingdom';

  FOR Country='United Kingdom';

  WHERE Country IS United Kingdom

  FOR Country==United Kingdom;


Report an error