Fetching Information From the Database with Python

Python 3: Automating Your Job Tasks Superhero Level: Automate Database Tasks with Python 3
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.19
List Price:  €129.06
You save:  €36.87
£78.58
List Price:  £110.01
You save:  £31.43
CA$136.67
List Price:  CA$191.35
You save:  CA$54.67
A$150.59
List Price:  A$210.84
You save:  A$60.24
S$134.92
List Price:  S$188.90
You save:  S$53.97
HK$780.74
List Price:  HK$1,093.06
You save:  HK$312.32
CHF 91.36
List Price:  CHF 127.90
You save:  CHF 36.54
NOK kr1,064.83
List Price:  NOK kr1,490.80
You save:  NOK kr425.97
DKK kr687.94
List Price:  DKK kr963.14
You save:  DKK kr275.20
NZ$163.14
List Price:  NZ$228.40
You save:  NZ$65.26
د.إ367.26
List Price:  د.إ514.18
You save:  د.إ146.92
৳11,726.90
List Price:  ৳16,418.13
You save:  ৳4,691.23
₹8,323.81
List Price:  ₹11,653.68
You save:  ₹3,329.86
RM470.40
List Price:  RM658.58
You save:  RM188.18
₦144,735.52
List Price:  ₦202,635.52
You save:  ₦57,900
₨27,863.65
List Price:  ₨39,010.23
You save:  ₨11,146.57
฿3,652.73
List Price:  ฿5,113.97
You save:  ฿1,461.24
₺3,221.22
List Price:  ₺4,509.83
You save:  ₺1,288.61
B$515.31
List Price:  B$721.46
You save:  B$206.14
R1,838.13
List Price:  R2,573.45
You save:  R735.32
Лв180.33
List Price:  Лв252.47
You save:  Лв72.14
₩136,328.34
List Price:  ₩190,865.12
You save:  ₩54,536.78
₪367.16
List Price:  ₪514.04
You save:  ₪146.88
₱5,817.18
List Price:  ₱8,144.28
You save:  ₱2,327.10
¥15,665.20
List Price:  ¥21,931.91
You save:  ¥6,266.71
MX$1,664.50
List Price:  MX$2,330.37
You save:  MX$665.86
QR364.97
List Price:  QR510.97
You save:  QR146
P1,352.70
List Price:  P1,893.83
You save:  P541.13
KSh13,248.67
List Price:  KSh18,548.67
You save:  KSh5,300
E£4,709.52
List Price:  E£6,593.52
You save:  E£1,884
ብር5,750.98
List Price:  ብር8,051.60
You save:  ብር2,300.62
Kz84,938.73
List Price:  Kz118,917.63
You save:  Kz33,978.89
CLP$89,860.01
List Price:  CLP$125,807.61
You save:  CLP$35,947.60
CN¥710.98
List Price:  CN¥995.41
You save:  CN¥284.42
RD$5,874.34
List Price:  RD$8,224.32
You save:  RD$2,349.97
DA13,453.05
List Price:  DA18,834.81
You save:  DA5,381.76
FJ$226.58
List Price:  FJ$317.23
You save:  FJ$90.64
Q777.82
List Price:  Q1,088.99
You save:  Q311.16
GY$20,943.47
List Price:  GY$29,321.70
You save:  GY$8,378.22
ISK kr13,838.61
List Price:  ISK kr19,374.61
You save:  ISK kr5,536
DH991.17
List Price:  DH1,387.67
You save:  DH396.50
L1,767.86
List Price:  L2,475.08
You save:  L707.21
ден5,684.36
List Price:  ден7,958.33
You save:  ден2,273.97
MOP$804.86
List Price:  MOP$1,126.84
You save:  MOP$321.97
N$1,819.29
List Price:  N$2,547.09
You save:  N$727.79
C$3,684.41
List Price:  C$5,158.32
You save:  C$1,473.91
रु13,334.80
List Price:  रु18,669.25
You save:  रु5,334.45
S/373.79
List Price:  S/523.33
You save:  S/149.53
K389.03
List Price:  K544.66
You save:  K155.63
SAR375.02
List Price:  SAR525.05
You save:  SAR150.02
ZK2,610.17
List Price:  ZK3,654.34
You save:  ZK1,044.17
L458.69
List Price:  L642.19
You save:  L183.49
Kč2,277.99
List Price:  Kč3,189.28
You save:  Kč911.28
Ft35,684.59
List Price:  Ft49,959.85
You save:  Ft14,275.26
SEK kr1,068.91
List Price:  SEK kr1,496.52
You save:  SEK kr427.60
ARS$88,989.05
List Price:  ARS$124,588.23
You save:  ARS$35,599.18
Bs691.73
List Price:  Bs968.45
You save:  Bs276.72
COP$381,035.35
List Price:  COP$533,464.74
You save:  COP$152,429.38
₡51,327.13
List Price:  ₡71,860.04
You save:  ₡20,532.90
L2,473.92
List Price:  L3,463.59
You save:  L989.66
₲753,154.66
List Price:  ₲1,054,446.66
You save:  ₲301,291.99
$U3,830.21
List Price:  $U5,362.45
You save:  $U1,532.23
zł393.43
List Price:  zł550.82
You save:  zł157.39
Already have an account? Log In

Transcript

Hi, and welcome to this lecture. In this lecture, you are going to learn several other methods that are related to the fetch all method we've seen in the previous lecture in order to extract the results of an SQL query. First of all, as always, I have imported the psycho PG two module, then I have established the connection to the database, and of course, initialize the cursor. Then using the cursor we are going to execute the following SQL query. So we have select all from my staff that employees where the salary is between 40,050 5000. And looking at the table.

That would be all the employees in the table except Emily, David's who has a salary of 59,000. Okay, going back to the Python interpreter, let's execute this query. And now as we did before, let's create a variable records equals cursor dot fetch All. Now let's iterate over the list generated by the fetch all method. So for I, in records, print I. And indeed We have all the employees in the table returned, except Emily, David's.

Now there are two other methods that are related to the fetch all method, and that can help us fetch the results of a query. The first method is fetch one, so let's use it records equals cursor dot fetch one. And now let's iterate over the list. And you can see that we got an error type error non type object is not iterable. That's because at this point, the cursor already fetched all the records in the table that met this condition. So these were the records right here, and the fetch one method has nothing else to return.

Remember that the fetch one method only fetches the next row the next record in the table, which is a result of the query and since our rows are exhausted at this point, the method Has nothing else to fetch. In order to fix this, we are going to run our SQL query once again. And now let's use the fetch one method again. And let's iterate over the results and see what happens. Indeed, we have only the first record in the table that meets this condition. So that would be john smith.

But we have three remaining records in the table that meet the condition inside the SQL query. So let's see how to access each of them. In order to do that, we're going to use the fetch one method once again. And now let's perform the iteration as well. And indeed, now we get the second record in the table that makes the condition let's run the method again, this is a third record, this is the fourth record. And finally, if we run the fetch one method once again and use the same iteration, then we get the non type object is not iterable error, which means that the records have been exhausted.

Okay. The other method I want to show you is called fetch Mini. And as its name implies, fetches multiple results from an SQL query and actually fetches as many results as we specify in between its parentheses. So let's try this. Let's run the SQL query once again. And now we have records equals cursor dot fetch many.

And in between the parentheses of fetch many, we specify how many results we want to be returned from this SQL query. So size equals, and let's say we want only two rows to be returned, size equals two. Now let's use the same for loop once again. And indeed, we get only two results being returned this time as a result of our query. The fetch many method can prove to be very useful, especially when having large tables with hundreds or thousands of records, and you want to set a limit for the number of records being returned by an SQL query. Oh, Okay, so having that said, I'll see you in the next lecture.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.