Collecting information from Twitter with Python (MySQL and Python work together)

Store the acquired data in DB

The part that stores the collected Tweet data in the DB. Operate MySQL from Python using the driver for MySQL.

Driver for MySQL

Use the one installed when building the environment.

Sample code

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()

Operation check

(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.

Finally

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.

Recommended Posts

Collecting information from Twitter with Python (MySQL and Python work together)
Collecting information from Twitter with Python (Twitter API)
Collecting information from Twitter with Python (Environment construction)
Collecting information from Twitter with Python (morphological analysis with MeCab)
Receive textual data from mysql with python
Tweet from python with Twitter Developer + Tweepy
Create a decision tree from 0 with Python and understand it (5. Information Entropy)
Pulling songwriting, composition and arrangement information from the Tower Records site with Python
Touch MySQL from Python 3
Collecting tweets with Python
Use MySQL from Python
Use MySQL from Python
Automatic follow on Twitter with python and selenium! (RPA)
Crawling with Python and Twitter API 1-Simple search function
Get mail from Gmail and label it with Python3
[Python] Get user information and article information with Qiita API
[Basics of data science] Collecting data from RSS with python
Programming with Python and Tkinter
Try hitting the Twitter API quickly and easily with Python
Encryption and decryption with Python
Python and hardware-Using RS232C with Python-
Twitter graphing memo with Python
Get Twitter timeline with python
Use Twitter API with Python
Notes on importing data from MySQL or CSV with Python
Information obtained from tweet_id (Python)
Launch Cloud Datastore Emulator with docker-compose and work with python app
Use MySQL from Anaconda (python)
I tried follow management with Twitter API and Python (easy)
Streamline information gathering with the Twitter API and Slack bots
Hash with python and escape from a certain minister's egosa
python with pyenv and venv
Search engine work with python
[Python x Zapier] Get alert information and notify with Slack
With skype, notify with skype from python!
Get Alembic information with Python
Works with Python and R
Connection pooling with Python + MySQL
Python: Extract file information from shared drive with Google Drive API
Crawling with Python and Twitter API 2-Implementation of user search function
[python] Extract text from pdf and read characters aloud with Open-Jtalk
Try to display google map and geospatial information authority map with python
Operate Firefox with Selenium from python and save the screen capture
Extract database tables with CSV [ODBC connection from R and python]
[Python] Try to recognize characters from images with OpenCV and pyocr
Communicate with FX-5204PS with Python and PyUSB
Shining life with Python and OpenCV
Robot running with Arduino and python
Call C from Python with DragonFFI
Install Python 2.7.9 and Python 3.4.x with pip.
Neural network with OpenCV 3 and Python 3
AM modulation and demodulation with python
[Python] font family and font with matplotlib
Scraping with Node, Ruby and Python
Using Rstan from Python with PypeR
Python, yield, return, and sometimes yield from
Create folders from '01' to '12' with python
Scraping with Python, Selenium and Chromedriver
Scraping with Python and Beautiful Soup
Build Mysql + Python environment with docker
Read and use Python files from Python