Using sqlalchemy and pandas to read and write from and to a local sqlite database for NLP
This post includes code adapted from python for finance and trading algorithms udemy course and python for finance and trading algorithms udemy course notebooks and the documentation here
import pandas as pd
import numpy as np
import pandas_datareader.data as web
import datetime
import matplotlib.pyplot as plt
%matplotlib inline
df=pd.read_csv('nf_complete.csv')
df.columns
df[["year","title"]]
df_subset = df[["year", "title", "abstract", "theme", "War", 'Cold War',"Trade"]]
df_subset
df_subset.columns = ["year", "title", "abstract", "theme", "War", 'Cold War',"Trade"]
df_subset
import sqlalchemy as db
from sqlalchemy import create_engine
import sqlite3
import pandas as pd
engine = db.create_engine('sqlite:///nf_nlp.db')
connection = engine.connect()
metadata = db.MetaData()
nf_nlp_table = db.Table('nf_nlp_table', metadata,
db.Column('year',db.Integer, nullable=True, index=False),
db.Column('title',db.String, nullable=True),
db.Column('abstract',db.String, nullable=True),
db.Column('theme',db.String, nullable=True),
db.Column('War',db.Integer, nullable=True),
db.Column('Cold War',db.Integer, nullable=True),
db.Column('Trade', db.Integer, nullable=True)
)
metadata.create_all(engine) #Creates the table
nf_nlp_table
df_subset.to_sql('nf_nlp_table', con=engine, if_exists='append', index=False)
engine.execute("SELECT year, theme, title FROM nf_nlp_table LIMIT 10").fetchall()
sql = """
SELECT
year
, theme
, title
FROM nf_nlp_table
"""
cnxn = connection
df = pd.read_sql(sql, cnxn)
df.tail(30)