python code terminates when using read_sql_query

Question

I have two tables: trainSearcStream and SearchInfo. My code is supposed to merge the two tables based on a common column named SearchID. the problem is that the code runs for sometime and then terminates without reporting any error in my eclipse console. the number or rows in each tables is: trainSearchStream|392,356,948 and SearchInfo|112,159,462. the size of my database (which include other tables) is 40GB. Update: When I ran code in terminal I got a message saying: 'killed: 9'

import pandas as pd
import sqlite3


# Create connection.
con = sqlite3.connect(path + 'database.sqlite')

#read into a data frame
df = pd.read_sql_query("SELECT * FROM trainSearchStream, SearchInfo WHERE  trainSearchStream.SearchID = SearchInfo.SearchID;", con)

#save to file
df.to_csv(path + 'traindata.csv',index=False,encoding='utf-8')

Answer 1

As mentionned, the best way I think is to directly use sqlite and not python.

So the solution is, I guess, this:

sqlite> .mode csv
sqlite> .output test.csv
sqlite> SELECT * FROM trainSearchStream, SearchInfo WHERE  trainSearchStream.SearchID = SearchInfo.SearchID; /*your query here*/
sqlite> .output stdout

---

Sorry, that is not an answer but commenting with the code would have destroyed the indent and we are using python ...

Could you run this and give us the output please ? (what was printed)

import pandas as pd
import sqlite3


# Create connection.
con = sqlite3.connect(path + 'database.sqlite')

try:
    df = pd.read_sql_query("SELECT * FROM trainSearchStream, SearchInfo WHERE  trainSearchStream.SearchID = SearchInfo.SearchID;", con)
    print "read_sql_query went well"
except Exception, e:
    print "read_sql_query failed: "+ str(e))

try:
    df.to_csv(path + 'traindata.csv',index=False,encoding='utf-8')
    print "to_csv went well"
except Exception, e:
    print "to_csv failed: "+ str(e))