Acesso ao Mysql - Python2 apenas (nao funciona no Python3)


In [1]:
import time
import datetime
import MySQLdb
import pandas as pd

con = MySQLdb.connect(host="mysql1213.ixwebhosting.com",user="A953887_USRATMOS",passwd="A7m05_psw",db="A953887_ATMOS_DB")

print "\nCarregando dados..."
df = pd.read_sql("SELECT * from sr311 where id<5 limit 10", con)
print "\n_____________\nFINALZADO!"


Carregando dados...
---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
<ipython-input-1-77cf31e2680b> in <module>()
      7 
      8 print "\nCarregando dados..."
----> 9 df = pd.read_sql("SELECT * from sr311 where id<5 limit 10", con)
     10 print "\n_____________\nFINALZADO!"

/usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    476             sql, index_col=index_col, params=params,
    477             coerce_float=coerce_float, parse_dates=parse_dates,
--> 478             chunksize=chunksize)
    479 
    480     try:

/usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1504 
   1505         args = _convert_params(sql, params)
-> 1506         cursor = self.execute(*args)
   1507         columns = [col_desc[0] for col_desc in cursor.description]
   1508 

/usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc in execute(self, *args, **kwargs)
   1483 
   1484             ex = DatabaseError("Execution failed on sql '%s': %s" % (args[0], exc))
-> 1485             raise_with_traceback(ex)
   1486 
   1487     @staticmethod

/usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc in execute(self, *args, **kwargs)
   1472                 cur.execute(*args, **kwargs)
   1473             else:
-> 1474                 cur.execute(*args)
   1475             return cur
   1476         except Exception as exc:

/usr/lib/python2.7/dist-packages/MySQLdb/cursors.pyc in execute(self, query, args)
    172             del tb
    173             self.messages.append((exc, value))
--> 174             self.errorhandler(self, exc, value)
    175         self._executed = query
    176         if not self._defer_warnings: self._warning_check()

/usr/lib/python2.7/dist-packages/MySQLdb/connections.pyc in defaulterrorhandler(***failed resolving arguments***)
     34     del cursor
     35     del connection
---> 36     raise errorclass, errorvalue
     37 
     38 re_numeric_part = re.compile(r"^(\d+)")

DatabaseError: Execution failed on sql 'SELECT * from sr311 where id<5 limit 10': (1054, "Unknown column 'id' in 'where clause'")

In [41]:
df.describe()


Out[41]:
Rain_mm AirTCmin WS_msmin AirTCmax WS_msmax AirTCsd WS_mssd BP_mbar T108_C RH AirTC SlrW WindDirs WS_msa WindDir_SD1_WVT WindDir_D1_WVT WS_ms_S_WVT PanelTemperature Status Battery
count 10 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000 10 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000 10.00000 10 10.000000
mean 0 21.062000 0.095000 21.407000 2.300000 0.074800 0.643600 957.800000 0 69.504000 21.212000 643.370000 138.991000 1.110600 17.016200 148.379000 1.110800 28.83200 0 13.666000
std 0 2.657416 0.300416 2.691712 0.987421 0.012691 0.190899 0.632456 0 8.350347 2.671511 102.474756 57.387654 0.794478 9.288151 41.518643 0.794403 1.53828 0 0.018974
min 0 20.010000 0.000000 20.320000 0.950000 0.061000 0.339000 956.000000 0 45.750000 20.160000 560.300000 26.100000 0.142000 2.620000 77.190000 0.142000 28.04000 0 13.630000
25% 0 20.190000 0.000000 20.537500 1.700000 0.068750 0.547250 958.000000 0 71.977500 20.322500 590.075000 105.375000 0.593250 12.477500 114.050000 0.593250 28.21750 0 13.660000
50% 0 20.270000 0.000000 20.610000 1.700000 0.072000 0.627500 958.000000 0 72.065000 20.430000 627.250000 156.150000 0.844500 16.800000 166.850000 0.845500 28.36500 0 13.670000
75% 0 20.312500 0.000000 20.630000 3.200000 0.077500 0.648000 958.000000 0 72.310000 20.452500 642.650000 175.475000 1.542500 22.845000 176.200000 1.542500 28.56500 0 13.680000
max 0 28.620000 0.950000 29.060000 3.950000 0.107000 0.978000 958.000000 0 72.580000 28.810000 921.000000 203.400000 2.699000 33.370000 206.900000 2.699000 33.17000 0 13.690000

In [ ]:
servername = "200.132.24.235";
username = "csda";
password = "...!CrS!,,,";
dbname = "csda";
con = MySQLdb.connect(host=servername,user=username,passwd=password,db=dbname)
%timeit df_dados = pd.read_sql("SELECT * from sr311", con)
df_dados.head()

In [ ]: