losing information when creating a shapefile

[sotokan80_]

I m using ArcGIS 10.4.1 and NetworkAnalyst. The following code is used to read a table in a database and create a points shapefile:

import arcpy
from arcpy import env
import csv
import pyodbc
TABLE_NAME = "ad0127_2403$";
try:
   cn = pyodbc.connect('DRIVER={SQL Server};SERVER=*******;DATABASE=routing;trusted_connection=yes')
   sql = "select Row_ID, Sys_Date, Latitude, Longitude, Dop, Speed, Mobile_ID_1, is_pod from dbo."+TABLE_NAME+" where is_pod = 224 order by Sys_Date"
   cur = cn.cursor()
   cur.execute(sql)
   the_data = cur.fetchall()
   cn.commit()

   OPoint = arcpy.Point()
   pointGeometryList = []

   for row in the_data:
      OPoint.Y = float(row[2])
      OPoint.X = float(row[3])
      Speed = float(row[4])

      spRef = arcpy.SpatialReference("WGS 1984")
      pointGeometry = arcpy.PointGeometry(OPoint,spRef)
      pointGeometryList.append(pointGeometry)

   # Output the result
   # Copy features to shapefile
   arcpy.CopyFeatures_management(pointGeometryList,    r"C:\Users\*****\Desktop\ROUTES\testingRoutess\testing.shp")
   arcpy.AddXY_management(r"C:\Users\******\Desktop\ROUTES\testingRoutess\testing.shp")

except Exception as e:
# If an error occurred, print line number and error message
import traceback, sys
tb = sys.exc_info()[2]
print "An error occured on line %i" % tb.tb_lineno
print str(e)

The problem that i m facing has to do with the fact that the created shapefile's attribute table is missing crucial information.The source table has the following fields

the shapefile's attribute table has the following fields

Is there any way or python method to auto add more fields (i.e Speed) in the attribute table? Any help would be appreciated.

[DanPatterson_Retired]

you only create the x, y coordinates for the output.  do you have a common field that you can use in a join?

[sotokan80_]

The common fields are those with the coordinates. But I was trying to avoid joining the tables because i get too many unused fields or empty fields. Is there any other way to add fields in the attribute table?

[DanPatterson_Retired · Accepted solution]

You can use an insertcursor and do it that way, or you could simply add Delete Field in a loop.  This option is much simpler particularly if your field names remain the same between runs, as they might)

[JakeSkinner]

You can simplify this by running the Make XY Event Layer function in your script.  This will retain all of your attributes.  You can then use the CopyFeatures function to copy this to a shapefile.

[sotokan80_]

Can you give me a small tip on how to use the make X Y event function.Do i have to create a temporary table because the function's syntax specifies that there is an input table

[JakeSkinner]

You would reference your database table through a Database Connection.  In the below example I'm connecting to a SQL Server database (not a geodatabase) and referencing a table called XY, creating the event layer, then exporting to a shapefile:

import arcpy
from arcpy import env
env.workspace = r"Database Connections\SQLSERVER - DBO@NONGDB.sde"

table = "XY"

sr = arcpy.SpatialReference(4326)

arcpy.MakeXYEventLayer_management(table, "x", "y", "XY_Layer", sr)

arcpy.CopyFeatures_management("XY_Layer", r"D:\temp\python\XY_Points.shp")‍‍‍‍‍‍‍‍‍‍‍

[DanPatterson_Retired]

Jake... cool new tool in Pro... simplifies the process XY Table to Point