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

• Vao Tsun: please add couple of lines of file.csv to the post
• Daniel: @VaoTsun The content of the file.csv is added
• Charles Duffy: mywiki.wooledge.org/BashFAQ/001
• Charles Duffy: 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: 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

Answer 1

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.

• Charles Duffy: Excellent call. Might make it a temporary table (create temporary table), if the intent is to be transient.
• Daniel: @CharlesDuffy hi from shell script I recived error: ERROR: column "112940676229" does not exist in s, c, unnamed_join
• Charles Duffy: @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).

Answer 2

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'

• Daniel: 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: Ahh. Sorry 'bout that, didn't match up the two param styles.
• Charles Duffy: 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: @ Charles Duffy Sorry for my lack of experience too much time without posting here.
• Charles Duffy: NP, it happens. Anyhow -- with the edit above (curs.execute(query, {"msidn": msidn, "appid": appid})), is the code behaving better?