def test_uquery(self):
try:
import MySQLdb
except ImportError:
raise nose.SkipTest
frame = tm.makeTimeDataFrame()
drop_sql = "DROP TABLE IF EXISTS test_table"
cur = self.db.cursor()
cur.execute(drop_sql)
sql.write_frame(frame, name='test_table', con=self.db, flavor='mysql')
stmt = 'INSERT INTO test_table VALUES(2.314, -123.1, 1.234, 2.3)'
self.assertEqual(sql.uquery(stmt, con=self.db), 1)
try:
sys.stdout = StringIO()
self.assertRaises(MySQLdb.ProgrammingError,
sql.tquery,
'insert into blah values (1)',
con=self.db)
self.assertRaises(MySQLdb.ProgrammingError,
sql.tquery,
'insert into blah values (1)',
con=self.db,
retry=True)
finally:
sys.stdout = sys.__stdout__
def test_uquery(self):
frame = tm.makeTimeDataFrame()
sql.write_frame(frame, name="test_table", con=self.db)
stmt = "INSERT INTO test_table VALUES(2.314, -123.1, 1.234, 2.3)"
self.assertEqual(sql.uquery(stmt, con=self.db), 1)
try:
sys.stdout = StringIO()
self.assertRaises(sqlite3.OperationalError, sql.tquery, "insert into blah values (1)", con=self.db)
self.assertRaises(
sqlite3.OperationalError, sql.tquery, "insert into blah values (1)", con=self.db, retry=True
)
finally:
sys.stdout = sys.__stdout__
def test_uquery(self):
frame = tm.makeTimeDataFrame()
sql.write_frame(frame, name='test_table', con=self.db)
stmt = 'INSERT INTO test_table VALUES(2.314, -123.1, 1.234, 2.3)'
self.assertEqual(sql.uquery(stmt, con=self.db), 1)
try:
sys.stdout = StringIO()
self.assertRaises(sqlite3.OperationalError, sql.tquery,
'insert into blah values (1)', con=self.db)
self.assertRaises(sqlite3.OperationalError, sql.tquery,
'insert into blah values (1)', con=self.db,
retry=True)
finally:
sys.stdout = sys.__stdout__
def test_uquery(self):
try:
import MySQLdb
except ImportError:
raise nose.SkipTest("no MySQLdb")
frame = tm.makeTimeDataFrame()
drop_sql = "DROP TABLE IF EXISTS test_table"
cur = self.db.cursor()
cur.execute(drop_sql)
sql.write_frame(frame, name='test_table', con=self.db, flavor='mysql')
stmt = 'INSERT INTO test_table VALUES(2.314, -123.1, 1.234, 2.3)'
self.assertEqual(sql.uquery(stmt, con=self.db), 1)
try:
sys.stdout = StringIO()
self.assertRaises(MySQLdb.ProgrammingError, sql.tquery,
'insert into blah values (1)', con=self.db)
self.assertRaises(MySQLdb.ProgrammingError, sql.tquery,
'insert into blah values (1)', con=self.db,
retry=True)
finally:
sys.stdout = sys.__stdout__
#If for the first time you create a table, then the following 'if_exists=replace' works
#sql.write_frame(df, name='scrapUnivData', con=con, if_exists='replace', flavor='mysql')
#However, the approach 'if_exists=replace' does not work if you run your code 2nd time
#and want to rewrite the table!
#
#The suggestion is
#Create the table first and then use the following two seperate commands
#sql.uquery("DELETE FROM scrapUnivData", con)
#sql.write_frame(df, name='scrapUnivData', con=con, if_exists='append', flavor='mysql')
#
#Putting all together
#sql.write_frame(df, name='scrapUnivData', con=con,
# if_exists='replace', flavor='mysql')
sql.uquery("DELETE FROM scrapUnivData", con)
sql.write_frame(df, name='scrapUnivData', con=con,
if_exists='append', flavor='mysql')
#Close connection
con.close()
#Get connected to fetch the data from MySQL
con = mdb.connect('localhost','root','mysql','mydata')
df2 = sql.read_frame('select * from scrapUnivData', con)
#Close connection
con.close()
#Check whether the dataframe has been fecthed correctly
print len(df2.index)
print df2.columns
