Convert a csv database into posgres

In [1]:

    

## Python packages - you may have to pip install sqlalchemy, sqlalchemy_utils, and psycopg2.
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
import psycopg2
import pandas as pd


    

In [2]:

    

#In Python: Define a database name (we're using a dataset on births, so I call it 
# birth_db), and your username for your computer (CHANGE IT BELOW). 
dbname = 'autism-docs'
username = 'rangel'


    

In [3]:

    

## 'engine' is a connection to a database
## Here, we're using postgres, but sqlalchemy can connect to other things too.
engine = create_engine('postgres://%s@localhost/%s'%(username,dbname))
print (engine.url)


    

    




postgres://rangel@localhost/autism-docs

In [4]:

    

## create a database (if it doesn't exist)
if not database_exists(engine.url):
    create_database(engine.url)
print(database_exists(engine.url))


    

    




True

In [4]:

    

# read a database from CSV and load it into a pandas dataframe
df = pd.DataFrame.from_csv('articles-n-forums-posts.csv')
df.head(2)
text = df.loc[0,['text']].values
print(text)
text_short = df.loc[0,['text_short']].values
print(text_short)


    

    




[ 'For children with autism spectrum disorder (ASD), head banging is a common way to self-soothe and communicate needs. Both neurotypical and autistic babies and toddlers seek to recreate the rhythm that stimulated their vestibular system while in utero. Other rhythmic habits that fuel a child’s kinesthetic drive include head rolling, body rocking, biting, and thumb… \n']
[ 'For children with autism spectrum disorder (ASD), head banging is a common way to self-soothe and communicate needs. Both neurotypical and autistic babies and toddlers seek to recreate the rhythm that (...)']

In [6]:

    

## insert data into database from Python (proof of concept - this won't be useful for big data, of course)
df.to_sql('articles-n-forums-posts', engine, if_exists='replace')


    

In [7]:

    

## Now try the same queries, but in python!

# connect:
con = None
con = psycopg2.connect(database = dbname, user = username)

# query:
sql_query = """
SELECT * FROM \"articles-n-forums-posts\" WHERE title LIKE '%autism%';
"""
query = pd.read_sql_query(sql_query,con)

query.head()


    

    
Out[7]:







  

    

      

      
post id
      
category
      
href
      
source
      
text
      
title
      
user id
      
tokens
      
text_short
    
  
  

    

      
0
      
21
      
['category-autism-and-food']
      
https://www.autismparentingmagazine.com/sugary...
      
https://www.autismparentingmagazine.com/
      
Sivakumari asks: My daughter loves to eat ice-...
      
Q and A Do sugary foods make children with aut...
      
NaN
      
['q', 'and', 'a', 'do', 'sugary', 'foods', 'ma...
      
Sivakumari asks: My daughter loves to eat ice-...
    
    

      
1
      
259
      
forums
      
http://ehealthforum.com/health/autism-what-is-...
      
http://ehealthforum.com
      
I hope these information would help you to hav...
      
autism - what is it?
      
636885.0
      
['autism', 'what', 'is', 'it', 'i', 'hope', 't...
      
I hope these information would help you to hav...
    
    

      
2
      
268
      
forums
      
http://ehealthforum.com/health/how-to-prove-i-...
      
http://ehealthforum.com
      
As ridiculous as this probably sounds , my par...
      
How to prove I don't have autism
      
313822.0
      
['how', 'to', 'prove', 'i', 'don', 't', 'have'...
      
As ridiculous as this probably sounds , my par...
    
    

      
3
      
212
      
forums
      
http://ehealthforum.com/health/possible-autism...
      
http://ehealthforum.com
      
my nephew who is four years has very bad mood ...
      
Possible autism signs ?
      
55473.0
      
['possible', 'autism', 'signs', 'my', 'nephew'...
      
my nephew who is four years has very bad mood ...
    
    

      
4
      
213
      
forums
      
http://ehealthforum.com/health/possible-autism...
      
http://ehealthforum.com
      
Hi, welcome to the ehealth forum and I am glad...
      
Possible autism signs ?
      
239324.0
      
['possible', 'autism', 'signs', 'hi', 'welcome...
      
Hi, welcome to the ehealth forum and I am glad...
    
  

In [11]:

    

# query:
sql_query = """
SELECT * FROM \"articles-n-forums-posts\" WHERE \"post id\" in (268,212);
"""
query = pd.read_sql_query(sql_query,con)

query.head()


    

    
Out[11]:







  

    

      

      
post id
      
category
      
href
      
source
      
text
      
title
      
user id
      
tokens
      
text_short
    
  
  

    

      
0
      
212
      
forums
      
http://ehealthforum.com/health/possible-autism...
      
http://ehealthforum.com
      
my nephew who is four years has very bad mood ...
      
Possible autism signs ?
      
55473.0
      
['possible', 'autism', 'signs', 'my', 'nephew'...
      
my nephew who is four years has very bad mood ...
    
    

      
1
      
268
      
forums
      
http://ehealthforum.com/health/how-to-prove-i-...
      
http://ehealthforum.com
      
As ridiculous as this probably sounds , my par...
      
How to prove I don't have autism
      
313822.0
      
['how', 'to', 'prove', 'i', 'don', 't', 'have'...
      
As ridiculous as this probably sounds , my par...
    
  

In [ ]: