title: Reading data from text file to postgres with python
body: <p>I can read the content of a test file <code>test.plt</code> in the root directory into a <code>postgres</code>  table <code>tempo</code> using this script:</p>
  
  <pre><code>$cat test.plt
  Geolife trajectory
  WGS 84
  Altitude is in Feet
  Reserved 3
  0,2,255,My Track,0,0,2,8421376
  0
  39.9756783,116.3308383,0,131.2,39717.4473148148,2008-09-26,10:44:08
  39.9756649,116.3308749,0,131.2,39717.4473842593,2008-09-26,10:44:14
  39.97564,116.3308749,0,131.2,39717.4474189815,2008-09-26,10:44:17
  39.9756533,116.3308583,0,131.2,39717.4474537037,2008-09-26,10:44:20
  39.9756316,116.3308299,0,131.2,39717.4474884259,2008-09-26,10:44:23
  39.9753166,116.3306299,0,131.2,39717.4480324074,2008-09-26,10:45:10
  39.9753566,116.3305916,0,131.2,39717.4480671296,2008-09-26,10:45:13
  39.9753516,116.3305249,0,131.2,39717.4481018518,2008-09-26,10:45:16
  </code></pre>
  
  <p>Python script:</p>
  
  <pre><code>import psycopg2
  from config import config
  import os
  import glob
  
  query = "INSERT INTO tempo (lat, lon, flag, alt, passeddate, gpsdate, gpstime) VALUES (%s, %s, %s, %s, %s, %s, %s)"
  
  path = '~/Desktop/Data/'
  
  conn = None
  try:
      #read the connection parameters
      params = config()
      # connect to the PostgreSQL server
      conn = psycopg2.connect(**params)
      cur = conn.cursor()
  
      # INSERRT data to the database
      with open('test.plt') as file:
          file_content = file.readlines()[6:]
          values = [line.strip().split(',') for line in file_content]
          cur.executemany(query, values)
  
      cur.close()
          # commit the changes
      conn.commit()
  except (Exception, psycopg2.DatabaseError) as error:
      print(error)
  finally:
      if conn is not None:
          conn.close()
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>postgres=&gt; SELECT * FROM tempo;
   id |    lat     |     lon     | flag |  alt  |    passeddate    |  gpsdate   | gpstime  
  ----+------------+-------------+------+-------+------------------+------------+----------
      | 39.9756783 | 116.3308383 |    0 | 131.2 | 39717.4473148148 | 2008-09-26 | 10:44:08
      | 39.9756649 | 116.3308749 |    0 | 131.2 | 39717.4473842593 | 2008-09-26 | 10:44:14
      |   39.97564 | 116.3308749 |    0 | 131.2 | 39717.4474189815 | 2008-09-26 | 10:44:17
      | 39.9756533 | 116.3308583 |    0 | 131.2 | 39717.4474537037 | 2008-09-26 | 10:44:20
      | 39.9756316 | 116.3308299 |    0 | 131.2 | 39717.4474884259 | 2008-09-26 | 10:44:23
      | 39.9753166 | 116.3306299 |    0 | 131.2 | 39717.4480324074 | 2008-09-26 | 10:45:10
      | 39.9753566 | 116.3305916 |    0 | 131.2 | 39717.4480671296 | 2008-09-26 | 10:45:13
      | 39.9753516 | 116.3305249 |    0 | 131.2 | 39717.4481018518 | 2008-09-26 | 10:45:16
  (8 rows)
  </code></pre>
  
  <p>I can also get the names of all files in all sub-directories with the <code>.plt</code> extension (stripping the <code>.plt</code> ext ) by replacing the insert statement in the python script with:</p>
  
  <pre><code>for root, dirs, files in os.walk(path):
          for file in files:
              if file.endswith(".plt"):
                  print(file.strip('.plt'))
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>20081210001529
  20081113121334
  20081205143505
  20081029234123
  20081202145929
  20081204142253
  20081111234235
  20081118003844
  20081105110052
  20081023055305
  </code></pre>
  
  <p>As you can see, files are named in figures. The goal is to take each file name, insert it into the <code>id</code> field of the <code>tempo</code>table, then its contents into the rest of columns. Repeating this for each in file in all sub-directories. </p>
  
  <ol>
  <li>How do I modify my code so that the filename(<code>e.g. 20081210001529</code>) is added to the insert query (get inserted to the table)</li>
  </ol>
  
  <p>Using the code below, with the intent to read data from all files in sub-directories (i.e. having listed all file in code above) gives error with only the first file in the first sub-directory, listed.</p>
  
  <pre><code>query = "INSERT INTO tempo (lat, lon, flag, alt, passeddate, gpsdate, gpstime) VALUES (%s, %s, %s, %s, %s, %s, %s)"
  
  path = '~/Desktop/Data/'
  #Establish connection to postgres
  conn = None
  try:
      #read the connection parameters
      params = config()
      # connect to the PostgreSQL server
      conn = psycopg2.connect(**params)
      cur = conn.cursor()
  
      for root, dirs, files in os.walk(path):
          for file in files:
              if file.endswith(".plt"):
                  f = open(file, 'r')
                  content = f.readlines()[6:]
                  values = [lines.strip().split(',') for line in content]
                  cur.executemany(query, values)
  
      cur.close()
          # commit the changes
      conn.commit()
  except (Exception, psycopg2.DatabaseError) as error:
      print(error)
  finally:
      if conn is not None:
          conn.close()
  [Errno 2] No such file or directory: '20081210001529.plt'
  </code></pre>
  
  <p>I would appreciate your help on this task.</p>