title: Unable to store twitter responses into SQL Server
body: <p>I am trying to harvest data from twitter using the search API into a SQL database with the python package pyodbc.</p>
  
  <ul>
  <li><p>I am doing this based on keywords and storing the output in a MS SQL database which I have set up in SQL Server Express 2008</p>
  
  <ol>
  <li>The data should enter the "TweetTemp" table first</li>
  <li>Then permanently store into the "TweetBank" table</li>
  <li>Finally keep a record in the "TweetLog" table.</li>
  </ol></li>
  </ul>
  
  <p>My program is able to connect to my database, however no tweets have been harvested. Only keywords and the runID have been returned in my "TweetLog" table.</p>
  
  <p>The rest of the tables are completely null.
  The tweets are returned in the python output, but with a unexpected error above each returned tweet</p>
  
  <p><strong>Error message displayed from a tweet example:</strong></p>
  
  <pre><code>############### Unexpected error: &lt;class 'pyodbc.DataError'&gt; ##################################
          Tweet from @metallicalyrc Date: Tue Dec 30 12:03:04 +0000 2014
           September 27 1986: Cliff Burton dies in bus accident in Sweden. Truly a sad day. His legacy will live on forever. 
  </code></pre>
  
  <p>Does anyone know how to solve this, or come across this issue with pyodbc before? I've crawled the web, but no solutions to this error message. It seems that perhaps pyodbc isn't processing the data properly, and therefore its not being pushed to the SQL database properly as I can't see an issue with my sql logic. </p>
  
  <p><strong>Here is my code:</strong>   </p>
  
  <pre><code>import string, json, pprint
  import urllib
  import string, os, sys, subprocess, time
  
  import pyodbc
  import twitter
  
  from genericpath import exists
  from twitter.archiver import statuses
  from collections import Counter
  from lib2to3.btm_utils import tokens
  
  CONSUMER_KEY = 'MY KEY'
  CONSUMER_SECRET = 'MY SECRET'
  OAUTH_TOKEN = 'MY OATH TOKEN'
  OAUTH_TOKEN_SECRET = 'MUOATH TOKEN SECRET'
  auth = twitter.oauth.OAuth(OAUTH_TOKEN, OAUTH_TOKEN_SECRET,
                             CONSUMER_KEY, CONSUMER_SECRET)
  twitter_api = twitter.Twitter(auth=auth)
  print twitter_api
  
  conn = pyodbc.connect('DRIVER={SQL Server};SERVER=MYSERVERNAME;DATABASE=MYDBNAME;UID=sa;PWD=MYPASSWD')
  cur = conn.cursor()
  # connect to database and create a cursor to do some work
  
  harvest_list = ['metallica', 'james hetfield', 'lars ulrich', 'kirk hammett', 'rob trujillo', 'jason newsted', 'cliff burton']
  # harvest list separated in the database by keyword
  
  cur.execute("select max(isnull(batchid,0)) from tweetlog")
  batch_id_cur = cur.fetchall()
  # updated 3-8-2012
  if batch_id_cur[0][0] is None:
      batch_id = 0
  else:
      batch_id = batch_id_cur[0][0]+1
  # grabbing the last "batch id", if it exists in order to make log entries that make SOME sense
  
  for tweet_keyword in harvest_list: # for each keyword, do this
  
      cur.execute("""delete from tweetbanktemp where tweet_keyword = '"""+str(tweet_keyword)+"""'""")
  conn.commit()
  # whack the temp table in case didn't exit cleanly
  
  search_results = twitter_api.search.tweets(q=tweet_keyword, count =100)
  # search for the current keyword
  
  for tweet in search_results['statuses']:
          # some me the tweet, jerry!
          print "        Tweet from @%s Date: %s" % (tweet['user']['screen_name'].encode('utf-8'),tweet['created_at'])
          print "        ",tweet['text'].encode('utf-8'),"\n"
  
          try:
                  # try to to put each tweet in the temp table for now
                  cur.execute("""insert into TweetBankTemp (tweet_id, tweet_datetime, tweet_keyword, tweet, tweeter, lang)
                                           values ('"""+str(tweet['id_str'].encode('utf-8').replace("'","''").replace(';',''))+"""',
                                                   '"""+str(tweet['created_at'].encode('utf-8'))+"""',
                                                   '"""+str(tweet_keyword)+"""',
                                                   '"""+str(tweet['text'].encode('utf-8').replace("'","''").replace(';',''))+"""',
                                                   '"""+str(tweet['user']['screen_name'].encode('utf-8').replace("'","''").replace(';',''))+"""',
                                                   '"""+str(tweet['metadata']['iso_language_code'].encode('utf-8').replace("'","''").replace(';',''))+"""'
                                           ) """)
          except:
                  print "############### Unexpected error:", sys.exc_info()[0], "##################################"
  # backup in case something bad happens all the tweets arnt lost
  
          cur.execute("""insert into tweetbank (tweet_id, tweet_datetime, tweet_keyword, tweet, tweeter, lang)
           select * from tweetbanktemp where tweet_id NOT in
           (select distinct tweet_id from tweetbank)""")
  # take all the tweets DIDNT already have and put them in the REAL tweet table
  
          cur.execute("""delete from tweetbanktemp where tweet_keyword = '"""+str(tweet_keyword)+"""'""")
  # take all THESE out of the temp table to not interfere with the next keyword
  
          cur.execute("""insert into tweetlog (BatchId, keyword, RunDate, HarvestedThisRun, TotalHarvested) values
           (
           '"""+str(batch_id)+"""',
           '"""+str(tweet_keyword)+"""',
           getdate(),
           ((select count(*) from tweetbank where tweet_keyword = '"""+str(tweet_keyword)+"""')-(select top 1 isnull(TotalHarvested,0) from tweetlog where keyword = '"""+str(tweet_keyword)+"""' order by RunDate desc)),
           (select count(*) from tweetbank where tweet_keyword = '"""+str(tweet_keyword)+"""')
           )""")
  # add a record to the log table saying what happened
  
          conn.commit()
          # finish
  </code></pre>