1

I have a table that has a column of type uniqueidentifier. How do I insert the data into uid columns using pyodbc?

I am trying this code:

 cursor.execute("""
                   Insert into tablename (col1,col2)
                   values (?,?)
                """, 
               '67E616B4-7DBC-6D14-B0BA-0F7DE2F94AEE',
               '2E92D02D-B7DA-4DED-9816-26B2CF867FA2' )

col1 and col2 are of type uniqueidentifier.

I get this error:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting from a character string to uniqueidentifier. (8169) (SQLExecDirectW)')

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Meraj Hussain
  • 329
  • 1
  • 6
  • 24
  • Does [this](https://stackoverflow.com/a/20940482/5841306) answer your question ? – Barbaros Özhan Dec 19 '20 at 09:51
  • How do i do this using python? – Meraj Hussain Dec 19 '20 at 09:52
  • I wanted to know if there are any python libraries to handle this situation? I used UUID library but that doesn't help much. – Meraj Hussain Dec 19 '20 at 09:56
  • I was able to insert these values using pyobc 4.0.30, Python 3.7.1 and ODBC Driver 17. I wonder if the issue is due to a configuration in SQL server? Can you try to create a new table and insert them there? – Jason Cook Dec 19 '20 at 23:37
  • I use python 3.6. Could you please share the code? – Meraj Hussain Dec 23 '20 at 03:14
  • ```python cursor.execute('CREATE TABLE _test (col1 uniqueidentifier, col2 uniqueidentifier)').commit() ``` ```python cursor.execute( "INSERT INTO _test (col1,col2) VALUES (?,?)", '67E616B4-7DBC-6D14-B0BA-0F7DE2F94AEE', '2E92D02D-B7DA-4DED-9816-26B2CF867FA2' ).commit() ``` – Jason Cook Dec 23 '20 at 10:52
  • The example you provided works directly for me. Sorry comments don't allow me to format code easily. – Jason Cook Dec 23 '20 at 10:54

1 Answers1

1

You can use fast_to_sql which is an improved way to upload pandas dataframes to Microsoft SQL Server such as

df = pd.DataFrame({
"col1": ['67E616B4-7DBC-6D14-B0BA-0F7DE2F94AEE'],
"col2": ['2E92D02D-B7DA-4DED-9816-26B2CF867FA2']
})
    
 fts.fast_to_sql(df, "tablename", conn, if_exists="append", custom=None, temp=False)

where append option stands for inserting new values to the table

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • 1
    thank you! But this works for python 3.8+. I only have python 3.6. I cannot move to 3.8 for some reasons. Is there an alternative for this? – Meraj Hussain Dec 19 '20 at 13:01
  • `df.to_sql('tablename', con=conn, if_exists='append', index=False )` might be another option @MerajHussain – Barbaros Özhan Dec 19 '20 at 13:05