0

I am very new to Python so hopefully I will provide the info needed to answer the question. I am trying to update/insert records into a newly created database. I have created a connection to the database with no issues, I can even insert records. But when I am trying to figure out what to update/insert this is where I am having the issue. I have a dataframe (DF1) that contains 2 columns (GovernmentID, Geometry).

here is the code I am using:

DF1 = pd.read_csv("C:\\Users\\pvagis\\Documents\\Trajectories.csv")
    DF1 = DF1.astype({"GovernmentID": str, "Geometry": str})
    print(DF1.dtypes)
    cursor = sql_conn.cursor()
    i = 0
    while i <= (len(DF1)-1):
        DB_DATA={"DI_UWI":DF1.at[i,'GovernmentID'],
                 "DI_GEOM":DF1.at[i,'Geometry']}
        UpsertQuery = ("""
                 IF NOT EXISTS (select UWI from LAND_JEOPARDY_DI_API_WELL_PATHS where UWI = :DI_UWI)
                 INSERT INTO LAND_JEOPARDY_DI_API_WELL_PATHS (WELL_PATH, UWI) values (:DI_GEOM, :DI_UWI)
                 ELSE
                 UPDATE LAND_JEOPARDY_DI_API_WELL_PATHS SET WELL_PATH = :DI_GEOM where UWI = :DI_UWI
                 """)
        cursor.execute(UpsertQuery, DB_DATA)
        sys.stdout.write("\r{}% complete   ".format(int(i/len(DF1)*100)))
        i = i + 1
    sql_conn.commit()
    cursor.execute("Delete from LAND_JEOPARDY_DI_API_WELL_PATHS where WELL_PATH = 'nan'")
    sys.stdout.write("\r{}% complete   ".format(int(i/len(DF1)*100)))
    sql_conn.commit()
    cursor.close()
    sql_conn.close()

The 'UpsertQuery' is where I am having the issue

Thanks everyone

Noah_v
  • 1
  • 1
  • can you provide the traceback or describe the error? – M Z Jul 10 '20 at 21:02
  • Traceback (most recent call last): File "", line 26, in cursor.execute(UpsertQuery) Error: ('07002', '[07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (0) (SQLExecDirectW)') – Noah_v Jul 10 '20 at 21:04
  • make sure you're providing arguments when you run your execute line. `cursor.execute(UpsetQuery, args)` – M Z Jul 10 '20 at 21:07
  • So I modified the code above to use a dictionary in the cursor arguments, but now I am getting the following error: ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000'). Can dictionaries be used here? I know you can use '?' as markers, but I am not sure how that would work on multiple lines...if that makes any sense... – Noah_v Jul 11 '20 at 12:17
  • You could use an approach similar to the one described in the answer to [this question](https://stackoverflow.com/a/62388768/2144390). – Gord Thompson Jul 19 '20 at 12:16

0 Answers0