1 Reply Latest reply on Nov 4, 2015 3:02 PM by Rithesh Makkena

    Help on my Python API. Connecting from SQL server and creating extracts

    Rithesh Makkena

      Hello all I am trying to connect to SQL server and input those results into Tableau Extract, my script works only when there is one column and if i add more than one Column in Tableau Table def it fails

       

      Error i get

      "

      Traceback (most recent call last):

        File "C:\Python27\hack1.py", line 52, in <module>

          newrow.setCharString(1,line['TLD'])

        File "C:\Python27\lib\site-packages\dataextract\Base.py", line 341, in setCharString

          raise Exceptions.TableauException(result, wstring_at(tablib.TabGetLastErrorMessage()))

      TableauException: TableauException (303): type mismatch "

       

       

       

      #Code;

       

      import dataextract as tde # saves some typing, cause i'm a lazy fucker

      import os, time, pymssql # for file manipulation, script timing (not necc), database access!

       

       

      ###################### FOR YOUR PARAMETERS, SON! ######################

      sql = "Select * from einstein.dbo.TableauAPI_hack " # whatever

      sqlserverhost = 'gb

      sqlusername = 'username'

      sqlpassword = 'p1234'

      sqldatabase = 'einstein'

      rowoutput = False # for DEBUGGING data errors

      ###################### FOR YOUR PARAMETERS, SON! ######################

      dotsevery = 75

       

       

      start_time = time.time() # simple timing for test purposes

       

       

      mssql_db = pymssql.connect(host=sqlserverhost, user=sqlusername, password=sqlpassword, database=sqldatabase, as_dict=True) # as_dict very important

      mssql_cursor = mssql_db.cursor()

      mssql_cursor.execute(sql)

      results = mssql_cursor.fetchall()

      #DEBUG

      print results

      print ' '

      print '[ Note: Each . = ' +str(dotsevery)+ ' rows processed ]'

       

       

       

       

      # You can use this technique to perform the equivalent to an incremental refresh.

      tdefile = tde.Extract('pythonhack1.tde')

       

       

      # Tableau DataTypes Dictionary Creation

      #tdeTypes = {'INTEGER': 7, 'DOUBLE': 10, 'BOOLEAN': 11, 'DATE': 12, 'DATETIME': 13, 'DURATION': 14, 'CHAR_STRING': 15, 'UNICODE_STRING': 16}

      # If the extract already exists, we don't need to define the table.

      # We just use the existing table and skip steps 2 and 3.

      if tdefile.hasTable('Extract'):

          table = tdefile.openTable('Extract')

          tableDef = table.getTableDefinition()

      else:

          #Step 2: Create the tableDef

          tableDef = tde.TableDefinition()

          tableDef.addColumn('Content Type', tde.Type.CHAR_STRING)

          tableDef.addColumn('TLD', tde.Type.UNICODE_STRING)

       

       

          #Step 3: Create the table in the image of the tableDef

          table = tdefile.addTable('Extract',tableDef)

       

       

      #Step 4: Loop through the csv, grab all the data, put it into rows

      #and insert the rows into the table

       

       

         

      newrow = tde.Row(tableDef)

      for line in results:

          newrow.setCharString(0,line['Content Type'])

          newrow.setCharString(1,line['TLD'])

          table.insert(newrow)

       

       

      #Step 5: Close the tde

      tdefile.close()