Compound SQL INSERT statement

Question

How would I combine the following two statements to create a valid SQL query?

provider = os.path.basename(file)

cursor.execute("""INSERT into main_app_financialstatements 
                  (statement_id, provider_id***, url, date) 
                  VALUES (%s, %s***, %s, %s)""", 
                  (statement_id, provider***, url, date))

provider_id = SELECT id FROM main_app_provider WHERE provider=provider

In other words, I have the provider, and I need to SELECT the provider_id from another table in order to INSERT it into the main_app_financialstatements.

Ilion · Accepted Answer · 2012-01-22 23:13:13Z

1

cursor.execute("""INSERT into main_app_financialstatements
  (statement_id, provider_id, url, date)
VALUES (%s, (SELECT id FROM main_app_provider WHERE provider=%s), %s, %s)""",
  (statement_id, provider, url, date))

answered Jan 22, 2012 at 23:13

Ilion

6,8923 gold badges29 silver badges47 bronze badges

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

Comments

unutbu · Accepted Answer · 2012-01-22 23:44:47Z

1

You could use the INSERT ... SELECT ... FROM variant of the INSERT command:

provider = os.path.basename(file)

sql = """
    INSERT INTO main_app_financialstatements 
        (statement_id, provider_id, url, date)
    SELECT %s, id, %s, %s
    FROM main_app_provider
    WHERE provider = %s
    """ 
args = (statement_id, url, date, provider)
cursor.execute(sql, args)

answered Jan 22, 2012 at 23:44

unutbu

888k199 gold badges1.9k silver badges1.7k bronze badges

Collectives™ on Stack Overflow

Compound SQL INSERT statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related