Write a Python program to query a MySQL database and print the query results. De

Question

Write a Python program to query a MySQL database and print the query results. Details:

Make a connection to the MySQL database with this connection information:

Write and execute a SQL query that returns the department number (dno), department name (dname), project number (pno), project name (pname), sum of hours worked on the project as well as the number of employees working on the project. Only include records where the budget is greater than $140,000. Order by sum of hours worked descending.

Print out your query results. Note that tabs (" ") are used to line up the data in columns nicely.

To install the MySQL driver using Anaconda, run this command:

WorksOn Relational Database Schema

Explanation / Answer

If you have any doubts, please give me comment...

import mysql.connector

cnx = mysql.connector.connect(user='rlawrenc', password='test', host='cosc304.ok.ubc.ca', database='WorksOn')

cursor = cnx.cursor()

query = ("SELECT dno, dname, pno, pname, SUM(hours) AS hours, COUNT(*) AS numemp FROM dept NATURAL JOIN proj NATURAL JOIN workson WHERE budget>140000 GROUP BY dno, dname, pno, pname ORDER BY SUM(hours)")

cursor.execute(query)

print("pno pname dno dname hours numemp")

for(dno, dname, pno, pname, hours, numemp) in cursor:

print(pno," ",pname," ",dno," ",dname," ",hours," ", numemp)

cursor.close()

cnx.close()

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.