保存数据到sqlite从tweepy
问题描述:
我有这个代码保存数据在cvs文件现在我想保存该数据到sqlite或mysql,但它应该保存像它保存在cvs数据库中的一个一个记录 和一个问题 这个数据量将每月 我的想法是做一个网站,该网站有趋势的所有统计每天30-40MB的数据库我应该在我有1GB使用MySQL或PostgreSQL的事业+数据库,我想使用在Django保存数据到sqlite从tweepy
import tweepy
import csv #Import csv
auth = tweepy.auth.OAuthHandler('XXXXXX', 'XXXXXXX')
auth.set_access_token('XXX-XXX', 'XXX')
api = tweepy.API(auth)
# Open/Create a file to append data
csvFile = open('result.csv', 'a')
#Use csv Writer
csvWriter = csv.writer(csvFile)
for tweet in tweepy.Cursor(api.search,
q="google",
since="2016-05-22",
until="2016-05-23",
lang="en").items():
#Write a row to the csv file/ I use encode utf-8
csvWriter.writerow([tweet.created_at, tweet.text.encode('utf-8')])
print tweet.created_at, tweet.text
csvFile.close()
答
SQLITE3解决方案:
首先,创建一个数据库,在这个例子中我使用了twitter2.db。基本连接到SQLITE3和创建表:
import sqlite3
conn = sqlite3.connect('C:\\....\\twitter2.db')
c = conn.cursor()
c.execute("CREATE TABLE tweeter(text VARCHAR, created_at DATETIME")
做的工作:
import tweepy
import sqlite3
conn = sqlite3.connect('C:\\....\\Desktop\\twitter2.db')
x = conn.cursor()
consumer_key="..."
consumer_secret=".."
access_token=".."
access_token_secret=".."
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = tweepy.API(auth)
class CustomStreamListener(tweepy.StreamListener):
def __init__(self, api):
self.api = api
super(tweepy.StreamListener, self).__init__()
def on_status(self, status):
x.execute("""INSERT INTO tweeter (text,created_at) VALUES(?,?)""",
(status.text, status.created_at))
conn.commit()
def on_error(self, status_code):
print >> sys.stderr, 'Encountered error with status code:', status_code
return True
def on_timeout(self):
print >> sys.stderr, 'Timeout...'
return True
sapi = tweepy.streaming.Stream(auth, CustomStreamListener(api))
sapi.filter(track=['google'])
关闭连接:
conn.close()