pg8000 conn.run argument error/ SQL syntax issues inserting data with AWS Lambda

Question

I am using AWS Lambda with Python and pg8000 to insert records into a PostgreSQL RDS database. When conn.run() is called, I get argument errors or syntax errors.

INSERT_SQL:

INSERT INTO crypto_prices (coin, price_usd, timestamp, day_of_week)
VALUES ($1, $2, $3, $4)
ON CONFLICT (coin, timestamp) DO NOTHING;

'params' looks like:

('bitcoin', 115251, '2025-08-04T18:30:40.926246+00:00', 'Monday')

Error in AWS logs (if I use conn.run(INSERT_SQL, params)):

Failed to insert bitcoin: list index out of range

Error in AWS logs (if I use conn.run(INSERT_SQL, *params)):

Failed to insert bitcoin: Connection.run() takes from 2 to 4 positional arguments but 6 were given

Any help is super appreciated, thank you.

I would first look at the (lack-luster) documentation for pg8000. [ def run(self, sql, stream=None, types=None, **params):](github.com/tlocke/pg8000/blob/…). Minus the self parameter, there are 2 optional and 2 required. The second error you are getting seems to suggest that somehow you are sending more arguments than you expect. From what you provided, there simply isn't enough information about your situation to determine a solution, but I would try running it locally before publishing your code to a lambda. — Thomas
– Thomas, Commented Aug 4 at 20:06
Hey, it actually turned out to be a PG8000.native problem. I'll answer my own question and, if you care, you can see the details. Thanks again, I really do appreciate the time you guys take. — Lil' Big Boy
– Lil' Big Boy, Commented Aug 5 at 17:23

Mark B · Accepted Answer · 2025-08-05 14:24:11Z

1

When you wrap multiple values in parentheses ( ) you are creating a specific Python data type called a tuple. The method conn.run() does not accept a tuple as a parameter, it accepts named parameters.

So you need to change your code to pass in the named parameters, for example:

conn.run(INSERT_SQL, coin=params[0], price_usd=params[1], time=prams[2], day=params[3])

Or, if you can change how you build the params variable, to build it as a dict instead of a tuple, so that the final params variable looks like this (note I'm only hard-coding the values here because you haven't shown in your question how you are actually getting the values):

params = {
  coin: 'bitcoin', 
  price_usd: 115251, 
  time: '2025-08-04T18:30:40.926246+00:00', 
  day: 'Monday'
}

Then you can pass that to the SQL query like so:

conn.run(INSERT_SQL, **params)

In either case, using numbers for your placeholders in the SQL query ($1, $2), instead of names, is not a good pattern, and the pg8000 docs on using those are not very clear. It's a much better idea to use named placeholders in your SQL query like almost all the example in the pg8000 docs demonstrate, like this:

INSERT INTO crypto_prices (coin, price_usd, timestamp, day_of_week)
VALUES (:coin, :price_usd, :time, :day)
ON CONFLICT (coin, timestamp) DO NOTHING;

answered Aug 5 at 14:24

Mark B

203k27 gold badges339 silver badges332 bronze badges

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

1 Comment

Lil' Big Boy Aug 5 at 17:09

I tried this to no avail, but it actually turned out to be a PG8000.native problem. I'll answer my own question and, if you care, you can see the details. Thanks again, I really do appreciate the time you guys take.

Ale Sosa · Accepted Answer · 2025-08-04 23:50:05Z

0

It seems you are giving a tuple as a parameter and the function is expecting key word arguments.

You can try something like the following unpacking the values.

params = {
    '0': 'bitcoin',
    '1': 115251,
    '2': '2025-08-04T18:30:40.926246+00:00',
    '3': 'Monday'
}

conn.run(INSERT_SQL, **params)

answered Aug 4 at 23:50

Ale Sosa

2232 silver badges10 bronze badges

3 Comments

Lil' Big Boy Aug 5 at 12:33

I don't understand. I can't hardcode the values in my project as that defeats the point of grabbing data from an API. This is what that part of my script looks like: params = ( record["coin"], record["price_usd"], record["timestamp"], day_of_week, ) conn.run(INSERT_SQL, params) Please bare with me, this is my first pipeline and I'm teaching myself from my bedroom. I'm burning out worse than I thought possible and feel like I'm getting more confused by the minute.

Ale Sosa Aug 5 at 16:35

Try the following instead, this is going to avoid the hardcoded values. params = { '0': record["coin"], '1': record["price_usd"], '2': record["timestamp"], '3': day_of_week }

Lil' Big Boy Aug 5 at 17:07

Thank you so so much for taking the time. Even just someone offering something to work at was great, but it actually turned out to be a PG8000.native problem. I'll answer my own question and, if you care, you can see the details. Thanks again. It's great to know total strangers want to help.

Lil' Big Boy · Accepted Answer · 2025-08-05 18:12:44Z

The issue was with using pg8000.native

* I switched over to importing plain old pg8000
* Changed the SQL value placeholders from ?/$1 to %s
* Switched conn.run() to .execute() after creating a 'cursor' object:

cursor = conn.cursor()

cursor.execute(INSERT_SQL, params)

I never set out to use pg8000.native, but did it upon the suggestion of a chatbot after psycopg2 broke a different part of my pipeline design (I am not ready to start learning about containerisation today with this burnt-out brain!).

Thanks for anyone who got back to me, learning as you build for the first time can make you feel like you're totally lost at sea, when really there is land just over the horizon, and with that simple fix I have my first fully functioning, terraform provisioned, AWS hosted, event scheduled ETL pipeline. Now I'm a real boy!

Collectives™ on Stack Overflow

pg8000 conn.run argument error/ SQL syntax issues inserting data with AWS Lambda

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related