0

What I am up to is: I want to insert formatted values into a PostgreSQL Table which I created in Python.

What my code is doing: reading couple .txt files with many measurements which will be formatted and saved into one .txt file.

My code:

folder_path =(r"C:\Users\yokay\Desktop\bla") 
values_re = re.compile(r'(\t\d+\t-?\d+,?\d*(\t-?\d+,?\d+){71})')
outF = open("MessungenTestTest.txt", "a")
for filename in glob.glob(os.path.join(folder_path, '*.txt')):
    with open(filename) as lines:
        for line in lines:
            match = values_re.search(line)
            if match: 
                values = match.group(0)
                values = values[1:] 
                values = values.replace(',','.')
                values = values.replace('\t',',')
                outF.write(str(values)+"\n")
                print(values)
                insert_state = "INSERT INTO MessungenTest(MessungNr, FS22SI_1_CH_1_Sensor_1_CH_6_Crackmeter_Mikrometer, ...73 columns...) Values(?????)";
                cur.execute(insert_state)

The(?????) in Values stands for I don't know what to put in. I tried with %s,%s, etc. but i get an Error:

ProgrammingError: FEHLER: Syntaxfehler bei »%« LINE 1: ...22SI_1_CH_8_Sensor_6_CH_77_Crackmeter_Mikrometer) VALUES(%s)

Everything works fine till i try to insert the values into the table.

If this helps: the column "MessungNr" have the Datatype integer, the other 73 columns have the datatype numeric(10,2).

Thank you for your time!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
minzpulver
  • 37
  • 7
  • You're missing commas between your `?` placeholders. You also have to feed a tuple of values as an additional argument since your insert is parameterized. Also, your placeholder might be `%s` instead of `?`. Depends on what pgsql library you're using. – Scratch'N'Purr Oct 24 '18 at 07:01
  • Sorry i forget to write that the ? stands for I don't know what to put in. I tried it with %s and with commas but i get an error: Syntaxfehler bei »%« LINE 1: ...22SI_1_CH_8_Sensor_6_CH_77_Crackmeter_Mikrometer) VALUES(%s) – minzpulver Oct 24 '18 at 07:12
  • Possible duplicate of [Using INSERT with a PostgreSQL Database using Python](https://stackoverflow.com/questions/9075349/using-insert-with-a-postgresql-database-using-python) – stovfl Oct 24 '18 at 07:33
  • Thank you.I saw that post before, it doesn'st work for me. – minzpulver Oct 24 '18 at 07:40
  • The number of `%s` placeholders you put in your statement corresponds to the number of columns in the table. Therefore, if you have 74 columns in that table, you need to make sure you have 74 `%s`s, eg. `INSERT INTO mytable (col1, col2, col3, ..., col74) VALUES (%s, %s, %s, ..., %s)` – Scratch'N'Purr Oct 24 '18 at 07:46
  • I also tried that @Scratch'N'Purr but the error i get starts with the first %s in Values: ProgrammingError: FEHLER: Syntaxfehler bei »%« LINE 1: ...CH_8_Sensor_6_CH_77_Crackmeter_Mikrometer) VALUES(%s,%s,%s,%... – minzpulver Oct 24 '18 at 07:49
  • start again with the example code in the documentation. – Jasen Oct 24 '18 at 09:38
  • which example code do you mean @Jasen? – minzpulver Oct 24 '18 at 10:30

0 Answers0