'psycopg2.ProgrammingError: syntax error at or near "hsa00020"

I am trying to insert following fields in postgreSQL table using psycopg2.

'hsa12345', 'homo sapiens', 'carbohydrate metabolism', 'metabolic', 'ko00010', ['hsa00020', 'hsa00030', 'hsa00500', 'hsa00620', 'hsa00640']

But psycopg2 gives me following error.

Traceback (most recent call last):
  File "pathway_parameterized.py", line 173, in <module>
    cur.execute(q1)
psycopg2.ProgrammingError: syntax error at or near "hsa00020"
LINE 1: ...bohydrate metabolism', 'metabolic', 'ko00010', '['hsa00020',...

I am using following lines of code to do this task.

if (id.startswith('hsa')) and (sub_section == 'non-metabolic' or sub_section == 'metabolic'):
                q1 = """INSERT INTO %s.table (kegg_accno, organism, pathway_subclass, pathway_subsection, ko_id, related_path_id) VALUES ('%s', '%s', '%s', '%s', '%s', '%s');"""%(staging_schema, id, 'homo sapiens', pathway_subclass, sub_section, ko_id, rel_path_id)
                cur.execute(q1)
                con.commit()
                    
else:
  ('do nothing')

What changes I can make to do this parsing? Any help is highly appreciated.

pythonpostgresqlpsycopg2bashunix


Solution 1:[1]

You need to quote the strings to preserve the double quotes:

for value in 'temp/sample."sample.id1".genotypes.txt' 'temp/sample."sample.id2".genotypes.txt'; do 
  echo "$value"
done

Otherwise, writing some."thing" is identical to some.thing because the shell interprets the quotes.

Solution 2:[2]

You can also escape it :

for value in temp/sample.\"sample.id1\".genotypes.txt temp/sample.\"sample.id2\".genotypes.txt; do echo $value; done

Solution 3:[3]

for things like this, I like to use a slightly different approach that looks like a better design to me:

# make an array with the data
mapfile -t ary <<"EOF"
temp/sample."sample.id1".genotypes.txt
temp/sample."sample.id2".genotypes.txt
EOF

# use the data from the array
for f in "${ary[@]}"; do
    printf '%s\n' "$f"
done

It will make your life a bit easier if your data grows, and you can then very easily transfer it to another file. Of course, if it's only for a one-time use (e.g., you made an error when naming your files and you only want to rename them), then learn how to have Bash properly parse the quotes as shown in the other answers (escaping them or using single quotes).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 natdop
Solution 3 gniourf_gniourf

Related Questions