I’m trying to create and populate the following table: import sqlite3, urllib, j

Question

I’m trying to create and populate the following table:

import sqlite3, urllib, json

Geo_Table = '''create table Geo
(created_at date not null,
type varchar2 (10),
latitude varchar2 (30),
longitude varchar2 (30),
constraint Geo_PK
primary key (created_at)
);'''

With the following code:

conn = sqlite3.connect("Twitter_One_Day.db")
c = conn.cursor()
c.execute("drop table if exists Geo;")

c.execute(Geo_Table)

WebFD = urllib.request.urlopen("http://rasinsrv07.cstcis.cti.depaul.edu/CSC455/OneDayOfTweets.txt")

for i in range(10000):
tweets = WebFD.readline().decode("utf8")
try:
obj = json.loads(tweets)
created_at = obj['created_at']
type = obj['geo']['type']
latitude = obj['geo']['coordinates']
longitude = obj['geo']['coordinates']   
  
c.execute("insert or ignore into Geo values (?,?,?,?)", (created_at, type, latitude, longitude))

except ValueError:
pass

I’m specifically trying to grab all columns and parse this dictionary field from the file:

'geo': {'coordinates': [14.670275, 121.043955], 'type': 'Point'}

And have them inserted, so that 14.670275 falls into the latitude column, 121.0443955 into longitude, and Point into type, and so on.

The two most common errors I keep receiving are:

type = obj['geo']['type']

TypeError: 'NoneType' object is not subscriptable

And this when I try removing the 'geo' field in front:

c.execute("insert or ignore into Geo values (?,?,?,?)", (created_at, type, latitude, longitude))

InterfaceError: Error binding parameter 1 - probably unsupported type.

So from what I understand is that my json objects are not parsing and grabbing the correct fields of data (am I correct on this?) and if so, how can I rectify this?

Explanation / Answer

import sqlite3 conn = sqlite3.connect('twitter.db') c = conn.cursor() st='''CREATE TABLE Tweet ( created_at VARCHAR2(25), id VARCHAR2(25), text VARCHAR2(25), source VARCHAR2(25), in-reply_to_user_ID VARCHAR2(25), retweet_Count VARCHAR2(25) ); ''' c.execute(st) lineArray=open("file.txt").readlines() for elt in lineArray: print elt lineArray[0][:-1].split(', ') #Loads variables for elt in lineArray: currentRow = elt[:-1].split(", ") insert = """insert into Tweet values ('%s', '%s', '%s', %s, %s, %s)""" %("created_at", "id", "text", 'source', 'in-reply_to_user_ID', 'retweet_Count') print insert {"created_at":"Fri Oct 11 00:00:03 +0000 2013", "id":388453908911095800, "id_str":"388453908911095809", "text":"LAGI PUN VISITORS DATANG PUKUL 9 AH", "source":"TweetDeck", "truncated":false, "in_reply_to_status_id":null, "in_reply_to_status_id_str":null, "in_reply_to_user_id":null, "in_reply_to_user_id_str":null, "in_reply_to_screen_name":null, "user":{ "id":447800506, "id_str":"447800506", "name":"§yazwina·", "screen_name":"_SAireen", "location":"SSP", "url":"http://flavors.me/syazwinaaireen#", "description":"Absence makes the heart grow fonder. Stay us x @_DFitri's", "protected":false, "followers_count":806, "friends_count":702, "listed_count":2, "created_at":"Tue Dec 27 08:29:53 +0000 2011", "favourites_count":7478, "utc_offset":28800, "time_zone":"Beijing", "geo_enabled":true, "verified":false, "statuses_count":32558, "lang":"en", "contributors_enabled":false, "is_translator":false, "profile_background_color":"DBE9ED", "profile_background_image_url":"http://a0.twimg.com/profile_background_images/378800000056283804/65d84665fbb81deba13427e8078a3eff.png", "profile_background_image_url_https":"https://si0.twimg.com/profile_background_images/378800000056283804/65d84665fbb81deba13427e8078a3eff.png", "profile_background_tile":true, "profile_image_url":"http://a0.twimg.com/profile_images/378800000264138431/fd9d57bd1b1609f36fd7159499a94b6e_normal.jpeg", "profile_image_url_https":"https://si0.twimg.com/profile_images/378800000264138431/fd9d57bd1b1609f36fd7159499a94b6e_normal.jpeg", "profile_banner_url":"https://pbs.twimg.com/profile_banners/447800506/1369969522", "profile_link_color":"FA0096", "profile_sidebar_border_color":"FFFFFF", "profile_sidebar_fill_color":"E6F6F9", "profile_text_color":"333333", "profile_use_background_image":true, "default_profile":false, "default_profile_image":false, "following":null, "follow_request_sent":null, "notifications":null }, "geo":null, "coordinates":null, "place":null, "contributors":null, "retweet_count":0, "favorite_count":0, "entities":{ "hashtags":[], "symbols":[], "urls":[], "user_mentions":[] }, "favorited":false, "retweeted":false, "filter_level":"medium", "lang":"it"}

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.