psql loop in shell script

Question

I need to read from a file.csv 2 values and make more then 13.000 queries in a PostgreSQL.

It should be very simple task as you may see in the code above, but there are some issues.

#!/bin/bash


MSISDN=($(head file.csv | awk -F ";" '{print $1}' | sed -e "s/^/55/"))
APPID=($(head file.csv | awk -F ";" '{print $3}'))

NUMBER_OF_LINES=$(wc -l file.csv| grep -o "[0-9]*")

for i in $(seq 0 "$NUMBER_OF_LINES")
do

    export PGPASSWORD='MY_PASSWORD'
    psql -q -A -h VERY-LONG-HOST -U MYUSER -d DATABASE -p 1111 -t -c "select 'http://API-HOST/subscription/cancel?subscriptionId=' + s.subscription_id + '&phone=' + s.phone + '&enabled=0&statusId=7&notifyActionListeners=false&extraInfo=TICKET_NUMBER' from sbs.subscription s (nolock) join sbs.configuration c on s.configuration_id = c.configuration_id where c.application_id = ${APPID[$i]} and c.carrier_id = 2 and s.phone = ${MSISDN[$i]};"

done

when the code executes there is a error:

ERROR:  syntax error at or near "112940676229"
LINE 2: and c.carrier_id = 2 and s.phone = 55112940676229;

How can I work with multiples queries and close the connection in each of them before make another query, thus far how to solve the error showed before?

Example of the content in file.csv:

112940676229;Sevice;333                                               
113429402012;Sevice;929  
111429402013;Sevice;888
11240672940;Sevice;445
11320294034;Sevice;333
11429294056;Sevice;22
11942940281;Sevice;122
11962940895;Sevice;233

Short form: Don't try to read file.csv into your arrays ahead-of-time; rather, read it line-by-line, and process each line as you go. — Charles Duffy
– Charles Duffy, Commented Aug 1, 2017 at 17:55
BTW, using string substitution to form SQL commands is prone to injection vulnerabilities. If you didn't build that CSV file yourself, by hand, I strongly advise against this approach -- you'd be much safer using a language that lets you use bind variables so you can pass data to your database engine out-of-band from code. Related: xkcd.com/327 — Charles Duffy
– Charles Duffy, Commented Aug 1, 2017 at 17:56

Vao Tsun · Accepted Answer · 2017-08-01 18:13:26Z

3

I would recommend you trying such approach:

create table file_csv (tel bigint, dk text, apid int);
copy file_csv from file.csv delimiter ';';

and then just join against it in your query.

update Thanks to @Charles Duffy

Might make it a temporary table (create temporary table file_csv (tel bigint, dk text, apid int)), if the intent is to be transient.

edited Aug 1, 2017 at 18:13

answered Aug 1, 2017 at 18:02

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charles Duffy Over a year ago

Excellent call. Might make it a temporary table (create temporary table), if the intent is to be transient.

Daniel Over a year ago

@CharlesDuffy hi from shell script I recived error: ERROR: column "112940676229" does not exist in s, c, unnamed_join

Charles Duffy Over a year ago

@Daniel, questions about my answer should be comments on my answer, not on someone else's. (And please provide output of running bash -x thescript to allow debugging).

Charles Duffy · Accepted Answer · 2017-08-02 13:11:25Z

1

For both security and performance, I strongly advise using a different language for this. That said, to answer the narrow question:

while IFS=';' read -r msidn _ appid _ <&3; do
  appid=${appid%$'\r'} # fix DOS newlines, should they be present in your input
  psql -q -A \
    -h VERY-LONG-HOST -U MYUSER -d DATABASE -p 1111 -t \
    -c "select 'http://API-HOST/subscription/cancel?subscriptionId=' + s.subscription_id + '&phone=' + s.phone + '&enabled=0&statusId=7&notifyActionListeners=false&extraInfo=TICKET_NUMBER' from sbs.subscription s (nolock) join sbs.configuration c on s.configuration_id = c.configuration_id where c.application_id = $appid and c.carrier_id = 2 and s.phone = $msidn;"
done 3< file.csv

That said, a less-awful Python implementation might look like:

#!/usr/bin/env python

import csv
import sys
import psycopg2

query='''
select
  s.subscription_id,
  s.phone
from
  sbs.subscription s (nolock)
  join sbs.configuration c on s.configuration_id = c.configuration_id
where
  c.application_id = %(appid)s
  and c.carrier_id = 2
  and s.phone = %(msidn)s
'''

# user is expected to pass CSV file name as our first argument
filename = sys.argv[1]

# user is expected to pass a PostgreSQL connect string as our second argument
conn = psycopg2.connect(sys.argv[2])

curs = conn.cursor()

with open(filename, 'rb') as csv_file:
  csv_reader = csv.reader(csv_file, delimiter=';')
  for csv_row in csv_reader:
    msidn = csv_row[0]
    appid = csv_row[2]
    curs.execute(query, {"msidn": msidn, "appid": appid})
    for result_row in curs.fetchall():
      sid = result_row[0]
      phone = result_row[1]
      print 'http://API-HOST/subscription/cancel?subscriptionId=' + sid + '&phone=' + phone + '&enabled=0&statusId=7&notifyActionListeners=false&extraInfo=TICKET_NUMBER'

edited Aug 2, 2017 at 13:11

answered Aug 1, 2017 at 18:00

Charles Duffy

299k43 gold badges441 silver badges496 bronze badges

9 Comments

Daniel Over a year ago

Hi @Charles Duffy, ok I had implement the Python code how ever I got this error: curs.execute(query, (appid,msidn)) TypeError: tuple indices must be integers or slices, not str I tried to cast the varibles msidn = int(csv_row[0]) appid = int(csv_row[2]) but with no success, I had give up the shell script because as you said it is too much agressive for this purpose.

Charles Duffy Over a year ago

Ahh. Sorry 'bout that, didn't match up the two param styles.

Charles Duffy Over a year ago

You'll still need the cast to int if the column types are integer -- I don't know what they types are since you didn't include the schema in the question. (If you'd included SQL to generate the schema and insert test data to fulfil the "Verifiable" part of the minimal reproducible example definition, then this answer could have actually been tested).

Daniel Over a year ago

@ Charles Duffy Sorry for my lack of experience too much time without posting here.

Charles Duffy Over a year ago

NP, it happens. Anyhow -- with the edit above (curs.execute(query, {"msidn": msidn, "appid": appid})), is the code behaving better?

|

Collectives™ on Stack Overflow

psql loop in shell script

2 Answers 2

3 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related