APPLICATION - Migrating Records from a Text File to the Database

Python 3: Automating Your Job Tasks Superhero Level: Automate Database Tasks with Python 3
9 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.67
You save:  €36.76
List Price:  £110.19
You save:  £31.48
List Price:  CA$190.60
You save:  CA$54.46
List Price:  A$208.90
You save:  A$59.69
List Price:  S$188.42
You save:  S$53.84
List Price:  HK$1,092.29
You save:  HK$312.10
CHF 90.89
List Price:  CHF 127.25
You save:  CHF 36.36
NOK kr1,073.95
List Price:  NOK kr1,503.58
You save:  NOK kr429.62
DKK kr686.39
List Price:  DKK kr960.97
You save:  DKK kr274.58
List Price:  NZ$228.20
You save:  NZ$65.20
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  ৳16,391.62
You save:  ৳4,683.65
List Price:  ₹11,661.24
You save:  ₹3,332.02
List Price:  RM656.20
You save:  RM187.50
List Price:  ₦205,785.30
You save:  ₦58,800
List Price:  ₨38,789.74
You save:  ₨11,083.57
List Price:  ฿5,074.35
You save:  ฿1,449.91
List Price:  ₺4,513.75
You save:  ₺1,289.73
List Price:  B$714.59
You save:  B$204.18
List Price:  R2,530.23
You save:  R722.97
List Price:  Лв251.81
You save:  Лв71.95
List Price:  ₩189,601.05
You save:  ₩54,175.60
List Price:  ₪518.60
You save:  ₪148.18
List Price:  ₱8,079.59
You save:  ₱2,308.62
List Price:  ¥21,792.94
You save:  ¥6,227
List Price:  MX$2,325.03
You save:  MX$664.34
List Price:  QR507.87
You save:  QR145.11
List Price:  P1,896.68
You save:  P541.94
List Price:  KSh18,338.69
You save:  KSh5,240
List Price:  E£6,566.93
You save:  E£1,876.40
List Price:  ብር8,018.62
You save:  ብር2,291.20
List Price:  Kz118,629.90
You save:  Kz33,896.68
List Price:  CLP$125,220.73
You save:  CLP$35,779.90
List Price:  CN¥1,011.17
You save:  CN¥288.92
List Price:  RD$8,157.46
You save:  RD$2,330.87
List Price:  DA18,810.98
You save:  DA5,374.95
List Price:  FJ$311.92
You save:  FJ$89.12
List Price:  Q1,087.56
You save:  Q310.75
List Price:  GY$29,285.87
You save:  GY$8,367.99
ISK kr13,827.61
List Price:  ISK kr19,359.21
You save:  ISK kr5,531.60
List Price:  DH1,386.51
You save:  DH396.17
List Price:  L2,464.07
You save:  L704.07
List Price:  ден7,916.55
You save:  ден2,262.03
List Price:  MOP$1,124.95
You save:  MOP$321.43
List Price:  N$2,547.77
You save:  N$727.98
List Price:  C$5,144.63
You save:  C$1,470
List Price:  रु18,661.67
You save:  रु5,332.28
List Price:  S/520.05
You save:  S/148.59
List Price:  K543.23
You save:  K155.22
List Price:  SAR524.96
You save:  SAR150
List Price:  ZK3,554.51
You save:  ZK1,015.64
List Price:  L640.62
You save:  L183.04
List Price:  Kč3,180.29
You save:  Kč908.72
List Price:  Ft49,855.26
You save:  Ft14,245.38
SEK kr1,071.59
List Price:  SEK kr1,500.27
You save:  SEK kr428.67
List Price:  ARS$124,136.13
You save:  ARS$35,470
List Price:  Bs967.19
You save:  Bs276.36
List Price:  COP$532,965.46
You save:  COP$152,286.72
List Price:  ₡71,639.71
You save:  ₡20,469.95
List Price:  L3,448
You save:  L985.21
List Price:  ₲1,048,812.96
You save:  ₲299,682.25
List Price:  $U5,395.05
You save:  $U1,541.55
List Price:  zł548.83
You save:  zł156.82
Already have an account? Log In


Hi, and welcome to this lecture. In this lecture, we are going to build an application that will migrate our data, our employees data from a text file into our database. So let's assume that during the time your company was pretty small, you chose to store the data regarding your employees into a simple text file, as you can see right here, so we have the ID, the first name, and last name, the department, the phone number, the address, and finally, the salary for each employee, where each of these pieces of information is delimited by a slash and a space. Now as your company grows, we are going to assume that you want to migrate all this data into a database and we already build the database earlier in this section. So all we have to do is find a way to automatically transfer this data into the database in order to Do that, let's open up the Python interpreter.

And first of all, let's think about our application. So before anything else, let's import the necessary modules. So psycho PG two, because we will be required to connect and to perform various operations on our database. Next, we're going to open the file that txt file containing the employees, we are going to read the data from within this file, and then transfer that data over to the database. In order to do that, let's take it step by step. And first of all, let's open the file.

So F equals, we are using the open function as we did earlier in the course, I'm going to enter the path to my file, that would be DB app and employees dot txt. Okay, at this point, we have the file open for reading, right because reading is the default mode, the default access mode in which to open any file. Okay, the next thing we should do is to create an empty list right? Let's say equals an empty list. And inside this list, we are going to store all the lines inside the text file in the form of a list. For that, of course, we have to use the read lines method on our open file.

And then we should append each record each line in the file to our list. So let's do that by also iterating over the list generated by the read lines method. So for i in F dot read lines, now let's use our list records that append so we are appending something to our list to our newly created list, records dot append of i dot split. And we are going to use the slash space as a delimiter for each piece of data on each row in the file. Okay, now let's see our list records. Okay, looks good.

So what we did is we split each line in the file right here, using the slash space as a delimiter and obtained Separate lists for each row in the file. Now going back to the interpreter, notice that we obtained a list of lists where the each element of the outer list is a list in itself containing all the elements that were previously separated by slash space on each line in the file. Okay, so we currently have this list in memory. Now let's connect to our database by simply using the same code as we did before, namely, the Connect method from within the psycho PG two module. So the connection has been successfully established. The next thing we should do is of course, initialize the cursor.

So cursor equals connection dot cursor. Okay. And now using this cursor, we should insert all the data inside this list of lists into our database using of course the insert into SQL statement, as we did earlier in this lecture. In order to do that, let me first show you the code for inserting this data into the database. And I'm going to show you the entire application. So this is the code that we used inside the Python interpreter thus far inside this lecture.

And now we are iterating over the records list, as I said, so we are iterating over this list. And for each list that is a member of this outer list of this bigger list, we want to extract its elements and insert each of them in each of the columns on that particular row inside the database. So in order to iterate over this list of lists, of course, we are going to use a for loop so for i in records, we are going to use the cursor and execute this command right here. So we have insert into my staff dot employees nothing new thus far. Then in between parentheses we have the name of the columns for which we want to insert some data there. The keyword values right here.

And then inside the parentheses of values, we are going to use seven string format operators corresponding to each of the columns, each of the seven columns in our database. After entering the SQL command right here, of course, followed by a semi colon, and in between double quotes, we insert a comma, and then in between parentheses, we're specifying each of the elements that should be mapped to the string format operators in between the parentheses following the values keyword. So going back to our list right here, let me minimize this. And let's consider the first list within this bigger list. So we would have insert into my staff that employees the name of the columns, the values keyword, and then since this is a list as well, using indexes, we can reference each of the members of the list. So we have I have zero for the ID, which is one If one will be mapped to look, if two will be mapped to Philip and so on until I have six will be mapped to 52,000.

And since we are iterating over this bigger list, the same insert operation is done for each list that is a member of this bigger lists until the outer list is exhausted. Also we are using a try except block. So, we are trying to execute this code. If any errors occur then we are printing out this message right here an error was generated while inserting the records else. So, in case the code under the try clause generates no exception, we are printing out to the screen record inserted successfully. Finally, of course, we are committing the changes.

So saving the changes to the database, and of course, closing the connection. So, I think this application is pretty straightforward. We are basically reading all the data in the txt file. We are splitting each row using the slash Base delimiter. Then we are obtaining this list right here, this list of lists, actually. And then using indexes, we are extracting each element of those lists, and then inserting it into the database using the insert into SQL statement.

And these seven format operators right here. Okay, now it's time to test our application. First of all, let's go to the database. And let's see what data do we currently have inside the table. So we have the five records from the previous lectures. Let me delete all these records.

So delete from my staff, dot employees. Okay, now let's check the table again. And the table is currently empty. Now let's open up the windows command line. And let's run our application. So we have Python, the path to the application in my case, db app, slash DB app dot P, y, Enter.

And now let's wait For the application to be executed, and indeed we have connection to database was successful, and records inserted successfully. Okay, so it seems that our application works. Now, of course, we should check that inside the SQL by issuing the same command again, select all from my staff dot employees. And indeed, this time, we have the 10 records that we previously had inside the text file migrated successfully into the database. So I hope this application is going to be useful for you, and that you now understand why is it so important to learn how Python interacts with a database, in our case, a Postgres SQL database, of course, in the lecture that follows immediately after this video, and you can download the code for this application, you can study it carefully. Also, I advise you to read all the comments and keep the comments in order to be able to to easily reference the code six months from now, and of course, why not you can even enhance this application by adding more functionality to it for now, I'll stop here and I'll see you in the next lecture.

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.