0
\$\begingroup\$

I have written the below code to read a huge text file of size 20+ MB and insert the values to a PostgreSQL table. The code is working fine, but I'm facing below issues in the code.

1) The code is taking too long to complete (nearly 2 hour). Is there a way to make it faster? 2) I wrote a condition (if field6!=field8: in the code) to check and insert only those lines which are having a difference in values, but in the execution of the code similar values are also being executed.

3) Since I separate each value using space as a separator, the date "25.02.2019 17:17:08:" is split into two parts and at the last ":" is there, so I have to append those fields by converting them to TEXT fields. Is there any better option? 4) The code is not so perfect. Is there a way to generalise this without hardcoding the file name and assigning the fields manually?

import psycopg2
import time

start_time = time.perf_counter()
try:
  conn = psycopg2.connect(host="localhost", database="postgres", user="postgres",
                         password="postgres", port="5432")
  print('DB connected')

except (Exception, psycopg2.Error) as error:
        # Confirm unsuccessful connection and stop program execution.
        print ("Error while fetching data from PostgreSQL", error)
        print("Database connection unsuccessful.")
        quit()        
try:

    filepath = filePath='''/Users/linu/Downloads/log''' 

    table='staging.stock_dump'

    SQL="""DROP TABLE IF EXISTS """+  table + """;CREATE TABLE IF NOT EXISTS """+ table + """
      (created_date TEXT, product_sku TEXT, previous_stock TEXT, current_stock TEXT );"""

    cursor = conn.cursor()
    cursor.execute(SQL)
    conn.commit()
    with open(filePath, 'r') as file:
     for line in file:
        if 'Stock:' in line:
            fields=line.split(" ")
            date_part1=fields[0]
            date_part2=fields[1][:-1]
            sku=fields[3]
            prev_stock=fields[5]
            current_stock=fields[7]
            if prev_stock.strip()==current_stock.strip():
                continue
            else:
               #print("insert into " + table+"(created_date, product_sku, previous_stock , current_stock)" + " select CAST('" + date_part1+ " "+ date_part2 + "' AS TEXT)" +", CAST('"+sku+"' AS TEXT),CAST('" + prev_stock +"' AS TEXT),CAST('" +current_stock  + "' AS TEXT) ;")
               cursor.execute("insert into " + table+"(created_date, product_sku, previous_stock , current_stock)" + " select CAST('" + date_part1+ " "+ date_part2 + "' AS TEXT)" +", CAST('"+sku+"' AS TEXT),CAST('" + prev_stock +"' AS TEXT),CAST('" +current_stock  + "' AS TEXT);")

    conn.commit()       
    cursor.close()
    conn.close()
    print("Data loaded to DWH from text file")
    print("Data porting took %s seconds to finish---" % (time.perf_counter() - start_time))

except (Exception, psycopg2.Error) as error:
        print ("Error while fetching data from PostgreSQL", error)
        print("Error adding  information.")
        quit()

Sample part of the text file is given below,

25.02.2019 18:17:08: Runcode: admin
25.02.2019 17:17:08: Initialising stockitem processor
25.02.2019 17:17:08: Running stockitem processor
25.02.2019 17:17:09: Reading bunch of 50 new stocks...
25.02.2019 17:17:09: Reading bunch of 50 products...
25.02.2019 17:17:09: stopping execution after 1 exceptions!
25.02.2019 18:30:01: Runcode: admin
25.02.2019 17:30:01: Initialising stockitem processor
25.02.2019 17:30:01: Running stockitem processor
25.02.2019 17:30:02: Reading bunch of 50 products...
25.02.2019 17:30:02: stopping execution after 1 exceptions!
25.02.2019 18:45:01: Runcode: admin 
25.02.2019 19:30:01: Initialising stockitem processor
25.02.2019 19:30:01: Running stockitem processor
25.02.2019 19:30:02: Reading bunch of 50 products...
25.02.2019 19:30:02: stopping execution after 1 exceptions!
25.02.2019 20:45:01: Runcode: admin
25.02.2019 20:00:02: Initialising stockitem processor
25.02.2019 20:00:02: Running stockitem processor
25.02.2019 20:00:03: Reading bunch of 50 products...
25.02.2019 20:00:03: stopping execution after 1 exceptions!
25.02.2019 21:15:01: Runcode: admin 
26.02.2019 10:30:09: Reading bunch of 50 products...
26.02.2019 10:30:09: stopping execution after 1 exceptions!
26.02.2019 11:45:01: Runcode: admin
26.02.2019 11:00:01: Initialising stockitem processor
26.02.2019 11:00:01: Running stockitem processor
26.02.2019 11:00:02: Reading bunch of 50 new stocks...
26.02.2019 11:00:03: Reading bunch of 50 new stocks...
26.02.2019 11:00:03: Reading bunch of 50 products...
26.02.2019 11:00:05: mapped 66 stocks from openerp to magento
26.02.2019 11:00:05: Stock: 3982 from 1 to 99
26.02.2019 11:00:05: Stock: 4758 from 8 to 6
26.02.2019 11:00:05: Stock: 1814 from 53 to 68
26.02.2019 11:00:05: Stock: 0437-bio from 111 to 114
26.02.2019 11:00:05: Stock: 2924-bio from 247 to 252
26.02.2019 11:00:05: Stock: 4806 from 262 to 263
26.02.2019 11:00:05: Stock: 3893 from 117 to 121
26.02.2019 11:00:05: Stock: 1486 from 0 to 0
26.02.2019 11:00:05: Stock: 4134 from 115 to 151
26.02.2019 11:00:05: Stock: 0129 from 24 to 30
26.02.2019 11:00:05: Stock: 1534-bio from 99 to 107
26.02.2019 11:00:05: Stock: 3032-bio from 187 to 211
26.02.2019 11:00:05: Stock: 1533-bio from 143 to 202
26.02.2019 11:00:05: Stock: 3596 from 67 to 91
26.02.2019 11:00:05: Stock: 3816-bio from 147 to 160
26.02.2019 11:00:05: Stock: 4776 from 0 to 0
26.02.2019 11:00:05: Stock: 2914 from 196 to 208
26.02.2019 11:00:05: Stock: 5449 from 24 to 25
26.02.2019 11:00:05: Stock: 4395 from 1191 to 1336
26.02.2019 11:00:05: Stock: 4752 from 7 to 7
26.02.2019 11:00:05: Stock: 4361 from 1375 to 1472
26.02.2019 11:00:05: Stock: 4794-bio from 265 to 332
26.02.2019 11:00:05: Stock: 0964-bio from 19 to 79
26.02.2019 11:00:05: Stock: 4791-bio from 356 to 417
26.02.2019 11:00:05: Stock: 4142-bio from 229 to 299
26.02.2019 11:00:05: Stock: 3814-bio from 61 to 69
26.02.2019 11:00:05: Stock: 1386-bio from 72 to 93
26.02.2019 11:00:05: Stock: 1692-bio from 0 to 176
26.02.2019 11:00:05: Stock: 1201 from 49 to 64
26.02.2019 11:00:05: Stock: 1385-bio from 288 to 321
26.02.2019 11:00:05: Stock: 1390-bio from 176 to 187
26.02.2019 11:00:05: Stock: 4737-bio from 120 to 127
26.02.2019 11:00:05: Stock: 0014 from 167 to 180
26.02.2019 11:00:05: Stock: 3621 from 23 to 27
26.02.2019 11:00:05: Stock: 4792-bio from 264 to 278
26.02.2019 11:00:05: Stock: 3900-bio from 98 to 116
26.02.2019 11:00:05: Stock: 0391 from 31 to 28
26.02.2019 11:00:05: Stock: 1804 from 0 to 0
26.02.2019 11:00:05: Stock: 4797-bio from 354 to 414
26.02.2019 11:00:05: Stock: 4024 from 1278 to 1427
26.02.2019 11:00:05: Stock: 4026 from 1076 to 1132
26.02.2019 11:00:05: Stock: 4447 from 163 to 230
26.02.2019 11:00:05: Stock: 3606 from 133 to 157
26.02.2019 11:00:05: Stock: 3926 from 380 to 490
26.02.2019 11:00:05: Stock: OIL-K25-001 from 0 to 0
26.02.2019 11:00:05: Stock: 3604 from 999 to 1068
26.02.2019 11:00:05: Stock: 4303-bio from 4 to 104
26.02.2019 11:00:05: Stock: 4456-bio from 11 to 195
26.02.2019 11:00:05: Stock: 4451 from 174 to 199
26.02.2019 11:00:05: Stock: 4476 from 68 to 92
26.02.2019 11:00:05: Stock: 4521 from 92 to 112
26.02.2019 11:00:05: Stock: 4477-bio from 187 to 193
26.02.2019 11:00:05: Stock: 4478-bio from 186 to 188
26.02.2019 11:00:05: Stock: 4421 from 356 to 770
26.02.2019 11:00:05: Stock: 3957 from 1781 to 2069
26.02.2019 11:00:05: Stock: 4358 from 725 to 783
26.02.2019 11:00:05: Stock: 3956 from 389 to 458
26.02.2019 11:00:05: Stock: 4598 from 915 to 1106
26.02.2019 11:00:05: Stock: 4597 from 1163 to 1438
26.02.2019 11:00:05: Stock: 3662 from 69 to 70
26.02.2019 11:00:05: Stock: 3989 from 0 to 12
26.02.2019 11:00:05: Catched 0 exceptions
26.02.2019 12:15:03: Runcode: admin
26.02.2019 11:15:03: Initialising stockitem processor
26.02.2019 11:15:03: Running stockitem processor
26.02.2019 11:15:04: No new stocks were found
26.02.2019 11:15:04: Catched 0 exceptions
26.02.2019 12:30:17: Runcode: admin
26.02.2019 11:30:17: Initialising stockitem processor
26.02.2019 11:30:17: Running stockitem processor
26.02.2019 11:30:18: No new stocks were found
26.02.2019 11:30:18: Catched 0 exceptions
26.02.2019 12:45:04: Runcode: admin
26.02.2019 11:45:04: Initialising stockitem processor
26.02.2019 11:45:05: Running stockitem processor
26.02.2019 11:45:06: No new stocks were found
26.02.2019 11:45:06: Catched 0 exceptions
26.02.2019 13:00:01: Runcode: admin
\$\endgroup\$

1 Answer 1

0
\$\begingroup\$

A couple of things jump out immediately: You are only inserting data from lines with the string "Stock:" and this word occurs in exactly the same position in each line. Instead of asking for a search of the line you could check that a specific slice be checked.

# setup outside read loop
stock_str = " Stock: "
stock_pos = 21
stock_end = stock_pos + len(stock_str)

...

# right after you read a line
    if line[stock_pos:stock_end] != stock_str:
        continue

(An aside: I prefer to use continue to short circuit loops rather than creating another indent level that I have to look down to find the end of. To me a continue says 'This path is uninteresting' and the interesting path is all at the same level.)

Another option for you is to use the csv_reader in the csv module, which is part of the standard library. You can specify a field delimiter character as an option. In your case that is the blank. I don't think this will make your code faster, but as the reader returns a list of strings your code will be cleaner.

There is no need to copy the fields from the array to named variables. Use field[8] where you are using field8. Actually those are terrible names which tell the reader of the code nothing about what you expect in the field. Better names would be something like stock_to = 8, stock_from = 6 and you would then be inserting field[stock_from], field[stock_to] which immediately tells the reader what value is expected at that position.

You say you are writing to postgresql. In that case you should not be creating a new string to be passed to the database for each row. You should be using "prepared statements" where the variables are the only thing that changes with each insert call. The parsing of the statement is thus done only once, when prepare() is called. This will probably give you a good speed up.

You don't say how often you expect to run this code. That influences how much time you should spend on speeding it up. If it is to be run every hout then it will be worthwhile to optimize it more. A once a week run, then the above changes are good enough and the expense of your time doing the optimization will not be recovered.

If you do want to optimize more you should profile the code (see the standard library profile module doc). If that shows, as I suspect, that the insertion into the database is your slow step then you can set up threads, communicating with your read loop in the main thread through a synchronous queue or a message passing library like RabbitMQ. The database will then have lots of data queued up to go to disk as long as you are reading from the input file. Check execution times with timeit() to be sure your code is getting faster and have a "standard" input file to run against. Creating a threaded version of a database program is a lot of work but can give you significant speedup.

\$\endgroup\$
6
  • \$\begingroup\$ Thanks a lot for this brief explanation, I will try your suggestions and will update you, I have searched for this prepare statements but could not find any suitable post to guide me with an example, Since i'm new to python programming i'm learning information gradually from these blogs. I'm planning to execute this script 3 or 4 times a day. I have modified my script a little bit and now it is taking around 40-50 minutes to complete the execution, used the strip() method for comparing the strings and now only the differed values are taking for insertion. \$\endgroup\$
    – Linu
    Jul 12, 2019 at 9:37
  • \$\begingroup\$ If there is an example for the prepare() statement you can show to me, it will be of great help.Finally i need to insert it to the PostgreSQL database and if there is a way to reduce the execution time further more , then it will be really helpful. \$\endgroup\$
    – Linu
    Jul 12, 2019 at 9:39
  • \$\begingroup\$ Also this statement need to be called after the for loop right, But does it make any difference ? i tired it in the For loop but saw it was rather slow, So confused whether i have done anything wrong .Can you please guide? if line[stock_pos:stock_end] != stock_str: continue \$\endgroup\$
    – Linu
    Jul 12, 2019 at 9:41
  • \$\begingroup\$ Have you mentioned the prepare() like this ? PREPARE stock_plan (text, text, text, text) AS INSERT INTO staging.stock_dump VALUES($1, $2, $3, $4); EXECUTE stock_plan(date_part1+ date_part2 , sku, prev_stock, current_stock); \$\endgroup\$
    – Linu
    Jul 12, 2019 at 9:48
  • \$\begingroup\$ Good, I see you found Postgreql's PREPARE. Yes, printing will slow your program down, especially if you are printing to a "terminal" on your screen. Output is much slower than moving stuff around in memory. To update the screen your system needs to move the printed data to the terminal process, the terminal process needs to format that data for the width of the terminal, and then the windowing software needs to turn the characters of the text into bitmaps that can be moved into the screen buffer for the next screen update cycle. This is actually a lot more work than the DB call. \$\endgroup\$ Jul 12, 2019 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.