The part that stores the collected Tweet data in the DB. Operate MySQL from Python using the driver for MySQL.
Use the one installed when building the environment.
It should be quicker to see the sample code for the actual usage. If you do like the ʻexecute_sql ()` function in the sample, you can execute SQL in the specified DB.
#!/usr/bin/env python
# -*-coding:utf-8-*-
import MySQLdb
### Functions
def main():
local_db = {
"host": "localhost",
"user": "root",
"passwd": "vagrant",
"db_name": "twitter_logs"
}
#DB creation
create_db(local_db)
#Create table
create_hashtag_serch_table(local_db)
#Data store
hashtag_search_dict = {
"tweet_id": u"10000000001",
"datetime": u"2014-11-09 18:05:42",
"user_id": u"123456789",
"user_name": u"salinger",
"display_name": u"J.D. Salinger",
"search_hashtag": u"#salinger",
"raw_tweet": u"I want to be the catcher of the rye field. I know it's ridiculous. But that's the only thing I really want to be.",
"words": u"Rye,field,of,Catch,Role,、,Such,もof,To,I,Is,Nari,Want,Hmm,Is,Yo,。,Stupid,Teru,thing,Is,Know,Teru,Yo,。,But,、,ほHmmWhenう,To,Nari,Want,もof,When,Ichi,Cod,It,Shika,Absent,Ne,。",
"nouns": u"Rye,field,Role,thing,I,Hmm,thing,ほHmmとう,thing,It",
"verbs": u"Catch,Nari,Stupid,Teru,Know,Teru,Nari,Ichi",
"adjs": u"Absent"
}
insert_into_hashtag_search(local_db, hashtag_search_dict)
return True
def create_db(db_info):
connector = MySQLdb.connect(
host = db_info["host"],
user = db_info["user"],
passwd = db_info["passwd"],
charset = "utf8"
)
cursor = connector.cursor()
sql = u"""
CREATE DATABASE IF NOT EXISTS
%s
CHARACTER SET
utf8
;
""" %(db_info["db_name"])
cursor.execute(sql)
connector.commit()
cursor.close()
connector.close()
return True
def execute_sql(sql, db_info, is_commit = False):
connector = MySQLdb.connect(
host = db_info["host"],
user = db_info["user"],
passwd = db_info["passwd"],
db = db_info["db_name"],
charset = "utf8"
)
cursor = connector.cursor()
cursor.execute(sql)
if is_commit:
connector.commit()
cursor.close()
connector.close()
return True
def create_hashtag_serch_table(db_info):
sql = """
CREATE TABLE IF NOT EXISTS
hashtag_search(
id BIGINT PRIMARY KEY AUTO_INCREMENT,
tweet_id BIGINT,
datetime DATETIME,
user_id BIGINT,
user_name VARCHAR(50),
display_name VARCHAR(50),
search_hashtag VARCHAR(140),
raw_tweet TEXT,
words TEXT,
nouns TEXT,
verbs TEXT,
adjs TEXT
)
;
"""
execute_sql(sql, db_info, is_commit = True)
return True
def insert_into_hashtag_search(db_info, hashtag_search_dict):
sql = """
INSERT INTO
hashtag_search
VALUES(
NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'
)
;
""" %(
hashtag_search_dict["tweet_id"],
hashtag_search_dict["datetime"],
hashtag_search_dict["user_id"],
hashtag_search_dict["user_name"],
hashtag_search_dict["display_name"],
hashtag_search_dict["search_hashtag"],
hashtag_search_dict["raw_tweet"],
hashtag_search_dict["words"],
hashtag_search_dict["nouns"],
hashtag_search_dict["verbs"],
hashtag_search_dict["adjs"]
)
execute_sql(sql, db_info, is_commit = True)
return True
### Execute
if __name__ == "__main__":
main()
(twi-py) $ python mysql_tools.py
(twi-py) $ mysql -u root -pvagrant twitter_logs
mysql> SHOW tables;
+------------------------+
| Tables_in_twitter_logs |
+------------------------+
| hashtag_search |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM hashtag_search\G
*************************** 1. row ***************************
id: 1
tweet_id: 10000000001
datetime: 2014-11-09 18:05:42
user_id: 123456789
user_name: salinger
display_name: J.D. Salinger
search_hashtag: #salinger
raw_tweet:I want to be the catcher of the rye field. I know it's ridiculous. But that's the only thing I really want to be.
words:Rye,field,of,Catch,Role,、,Such,もof,To,I,Is,Nari,Want,Hmm,Is,Yo,。,Stupid,Teru,thing,Is,Know,Teru,Yo,。,But,、,ほHmmWhenう,To,Nari,Want,もof,When,Ichi,Cod,It,Shika,Absent,Ne,。
nouns:Rye,field,Role,thing,I,Hmm,thing,ほHmmとう,thing,It
verbs:Catch,Nari,Stupid,Teru,Know,Teru,Nari,Ichi
adjs:Absent
1 row in set (0.00 sec)
mysql>
I was able to confirm DB creation, Table creation, and data storage.
After that, if you combine the contents of the articles so far into one program and connect the input and output of each other well, You now have a basic program in Python for collecting information from Twitter. In the next article, that's about it.