0

I have database that has a column that is a Georgian date like{YYYYMMDD}. I want to have a column(or 3 for year, month and day) for Jalai date. I can easily write and script using python to convert Georgian date to Jalali. This is what I write in python:

import jdatetime as jdt
georgian = str(20170828)
def GtoJ(georgian):
    jalai = jdt.GregorianToJalali(int(georgian[:4]),int(georgian[4:6]),int(georgian[-2:]))
#     print('{}{:02}{:02}'.format(jalai.jyear,jalai.jmonth,jalai.jday))
    return '{}{:02}{:02}'.format(jalai.jyear,jalai.jmonth,jalai.jday)

print(GtoJ(georgian)) ##13960606

Assume I create an empty column in my postgresql database with name jdate, using sqlalchemy or any other sql engine I update every row with following codes:

for date in history_tabel_date:
    jdate = GtoJ(str(date))
    con.execute('update history_tale jdate= value_jdate where date = {}'.format(date))

What is the way to write a sql function that do the same thing?

Generally is it a good idea to write script outside sql or sql functions are better and recommended?

Mehdi
  • 1,260
  • 2
  • 16
  • 36

1 Answers1

1

When using a database as a resource for a script, the bottleneck occurs when the the script and the database are going back and forth due to creating a connection, transporting the data, etc. Therefore, you should be looking towards doing most of the data processing at either end and reducing the number of queries (INSERTS, UPDATES, DELETES) to the bare minimum.

If you're looking at performing the function on the database side, you could consider using PL/Python which is a

procedural language allows PostgreSQL functions to be written in the Python language.

aydow
  • 3,673
  • 2
  • 23
  • 40
  • that's great aydow, In documentations I didn't see any import statement, can I import packages that I installed using pip? – Mehdi Aug 30 '17 at 07:07
  • [import statements](https://stackoverflow.com/questions/15023080/how-are-import-statements-in-plpython-handled) – aydow Aug 30 '17 at 07:18
  • i think this might help for [importing pip-installed libraries](https://blag.felixhummel.de/postgresql/plpython.html). i haven't used it before but if the library is in your `PYTHONPATH`, then it should work.. – aydow Aug 30 '17 at 07:18
  • hi @Mehdi, could you please accept or at least up vote the answer considering that it was helpful to you? many thanks – aydow Oct 23 '17 at 00:03