0

I'm sure this is straightforward but I've been staring at this for ages and can't see the wood for the trees. I have a MySQL database (myDB) and I've created a sqlalchemy engine using:

myEng = sqlalchemy.create_engine('mysql+pymysql://{0}:{1}@localhost/myDB'.format(myUsername,myPassword))

I can insert a row of data into the myDB database using:

with myEng.begin() as conn:     
    conn.execute('INSERT into mytbl (col1int,col3str) VALUES (99,'some text'))

That works fine and as expected.

However, instead of hard-coding values into the query, I want to insert values that are stored in a dictionary or other variables such as:

myDict = {'intVal':999,
          'strVal':'some text')

Based on several websites, I've tried to add the same data to the table using:

with myEng.begin() as conn:     
    conn.execute('INSERT into mytbl (col1int,col3str) VALUES (:myInt,:myStr)',myInt = myDict['intVal'],myStr = myDict['strVal'])

However, I get a variety of errors (depending on variation of above) including ProgrammingError suggesting there is an error in SQL syntax and TypeError indicating that not all arguments converted during string formatting.

Many apologies if I'm just not seeing a typo!

user1718097
  • 4,090
  • 11
  • 48
  • 63
  • 1
    (1) You need to wrap your query in a SQLAlchemy `text` object in order to use named parameters. (2) You should supply the parameter names and values by passing the entire Python `dict`. – Gord Thompson Feb 02 '20 at 13:05
  • @Gord Thompson Your suggestion wasn't the same as instructions I had found online but it absolutely did the trick! Thanks for the help – I could have been barking up the wrong tree for hours more. – user1718097 Feb 02 '20 at 13:34

0 Answers0