Inserting Records Into a Table with Python

Python 3: Automating Your Job Tasks Superhero Level: Automate Database Tasks with Python 3
3 minutes
Share the link to this page
You need to have access to the item to view this lesson.
One-time Fee
List Price:  $139.99
You save:  $40
List Price:  €128.82
You save:  €36.80
List Price:  £109.74
You save:  £31.35
List Price:  CA$191.13
You save:  CA$54.61
List Price:  A$210.68
You save:  A$60.19
List Price:  S$188.83
You save:  S$53.95
List Price:  HK$1,092.95
You save:  HK$312.29
CHF 91.34
List Price:  CHF 127.88
You save:  CHF 36.54
NOK kr1,052.31
List Price:  NOK kr1,473.28
You save:  NOK kr420.96
DKK kr686.58
List Price:  DKK kr961.24
You save:  DKK kr274.66
List Price:  NZ$227.97
You save:  NZ$65.13
List Price:  د.إ514.17
You save:  د.إ146.91
List Price:  ৳16,408.23
You save:  ৳4,688.40
List Price:  ₹11,637.16
You save:  ₹3,325.14
List Price:  RM657.67
You save:  RM187.92
List Price:  ₦206,269.66
You save:  ₦58,938.40
List Price:  ₨38,916.02
You save:  ₨11,119.65
List Price:  ฿5,127.43
You save:  ฿1,465.08
List Price:  ₺4,505.30
You save:  ₺1,287.32
List Price:  B$723.91
You save:  B$206.84
List Price:  R2,571.91
You save:  R734.88
List Price:  Лв252.38
You save:  Лв72.11
List Price:  ₩190,499.14
You save:  ₩54,432.21
List Price:  ₪515.38
You save:  ₪147.26
List Price:  ₱8,140.48
You save:  ₱2,326.02
List Price:  ¥21,963.82
You save:  ¥6,275.82
List Price:  MX$2,336.09
You save:  MX$667.50
List Price:  QR510.10
You save:  QR145.75
List Price:  P1,900.64
You save:  P543.07
List Price:  KSh18,618.67
You save:  KSh5,320
List Price:  E£6,599.78
You save:  E£1,885.78
List Price:  ብር8,037.80
You save:  ብር2,296.67
List Price:  Kz118,908.34
You save:  Kz33,976.24
List Price:  CLP$126,117.11
You save:  CLP$36,036.03
List Price:  CN¥1,014.10
You save:  CN¥289.76
List Price:  RD$8,237.97
You save:  RD$2,353.87
List Price:  DA18,843.53
You save:  DA5,384.25
List Price:  FJ$311.92
You save:  FJ$89.12
List Price:  Q1,086.64
You save:  Q310.49
List Price:  GY$29,266.81
You save:  GY$8,362.54
ISK kr13,793.62
List Price:  ISK kr19,311.62
You save:  ISK kr5,518
List Price:  DH1,395.33
You save:  DH398.69
List Price:  L2,480.56
You save:  L708.78
List Price:  ден7,930.17
You save:  ден2,265.92
List Price:  MOP$1,125.70
You save:  MOP$321.65
List Price:  N$2,570.90
You save:  N$734.59
List Price:  C$5,148.73
You save:  C$1,471.17
List Price:  रु18,603.56
You save:  रु5,315.68
List Price:  S/522.95
You save:  S/149.42
List Price:  K543.64
You save:  K155.33
List Price:  SAR525.05
You save:  SAR150.02
List Price:  ZK3,733.66
You save:  ZK1,066.83
List Price:  L641.14
You save:  L183.19
List Price:  Kč3,187.01
You save:  Kč910.64
List Price:  Ft49,549.41
You save:  Ft14,157.98
SEK kr1,062.40
List Price:  SEK kr1,487.41
You save:  SEK kr425
List Price:  ARS$124,696.09
You save:  ARS$35,630
List Price:  Bs966.60
You save:  Bs276.19
List Price:  COP$540,370.17
You save:  COP$154,402.50
List Price:  ₡71,720.93
You save:  ₡20,493.15
List Price:  L3,457
You save:  L987.78
List Price:  ₲1,052,195.01
You save:  ₲300,648.62
List Price:  $U5,389.29
You save:  $U1,539.90
List Price:  zł547.82
You save:  zł156.53
Already have an account? Log In


Okay, so in the previous lecture, we have created the table the employees table with the seven columns that we discussed. Next, if we issue a query on this table using the Select all from my staff that employees query, we will see that there are currently zero rows inside this table. So no data has been added yet. In order to insert data into this table, let's see the SQL syntax for this operation. So we have insert into the name of the schema, a dot and then the name of the table. Then inside parentheses, we enter the name of the columns for which we want to insert some data.

Then we have the keyword values. And again in between parentheses, we entered the data to be inserted for each of these columns. If we want to insert more than one row of data into the table, then we can use a comma Ma and open another set of parentheses in which we can specify the data to be inserted on the second row in the table and so on until we insert all the necessary data. Of course, at the end, you shouldn't forget to add the semi colon, which is mandatory for every SQL command. Now translating this into our Python code, this code right here, we've already discussed and tested. Now it's time to use the cursor and execute this insert command.

However, I should mention that the command should be enclosed by quotes. And instead of triple quotes for a command spanning multiple lines we are using after each line, the continuation character backslash, which is used in Python to continue the code on the next line. Finally, of course, we commit our changes and close the connection. Next inside the Python interpreter. I have imported the necessary module I have established the connection created the cursor as we did thus far in the section, there is no need to create the table. Once again, since the table has been already created in the previous lecture, we have committed that change.

And now the table exists in our database. The only thing to do is to copy and paste this command right here in order to insert the data into the table. So let me paste this right here. Enter, the command has been executed. Now it's time to commit the changes once again. So connection dot commit.

And finally connection dot close. And now let's go over to P SQL and query our table once again. And this time you can see that the data inside the insert command has been entered into the table. So for now, we have five employees, each with its own ID, first name, last name, department, phone address, and salary. Okay, so this is the way in which you can insert data into a table Down below immediately after this lecture, you will find the code that we used inside this video so you can copy and paste it inside your Python interpreter or Python applications. I'll see you in the next lecture, where we're going to discuss how to update records in a database.

Sign Up


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.