You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
data-transport/notebooks/mysql.ipynb

4.3 KiB

None <html lang="en"> <head> </head>

Writing to MySQL

  1. Insure MySQL is actually installed on the system,
  2. There is a database called demo created on the said system

The cell below creates a dataframe that will be stored within postgreSQL

In [3]:
#
# Writing to PostgreSQL database
#
import transport
from transport import providers
import pandas as pd
_data = pd.DataFrame({"name":['James Bond','Steve Rogers','Steve Nyemba'],'age':[55,150,44]})
myw = transport.get.writer(provider=providers.MYSQL,database='demo',table='friends',auth_file="/home/steve/auth-mysql.json")
myw.write(_data,if_exists='replace') #-- default is append
print (transport.__version__)
2.0.4

Reading from MySQL

The cell below reads the data that has been written by the cell above and computes the average age within a MySQL (simple query).

  • Basic read of the designated table (friends) created above
  • Execute an aggregate SQL against the table

NOTE

By design read object are separated from write objects in order to avoid accidental writes to the database. Read objects are created with transport.get.reader whereas write objects are created with transport.get.writer

In [4]:
import transport
from transport import providers
myr = transport.get.reader(provider=providers.MYSQL,database='demo',table='friends',auth_file='/home/steve/auth-mysql.json')
_df = myr.read()
_query = 'SELECT COUNT(*) _counts, AVG(age) from friends'
_sdf = myr.read(sql=_query)
print (_df)
print ('--------- STATISTICS ------------')
print (_sdf)
           name  age
0    James Bond   55
1  Steve Rogers  150
2  Steve Nyemba   44
--------- STATISTICS ------------
   _counts  AVG(age)
0        3      83.0

An auth-file is a file that contains database parameters used to access the database. For code in shared environments, we recommend

  1. Having the auth-file stored on disk
  2. and the location of the file is set to an environment variable.

To generate a template of the auth-file open the file generator wizard found at visit https://healthcareio.the-phi.com/data-transport

In [5]:
{
    "host":"klingon.io","port":3306,"username":"me","password":"foobar", "provider":"mysql",
    "database":"demo","table":"friends"
}
Out[5]:
{'host': 'klingon.io',
 'port': 3306,
 'username': 'me',
 'password': 'foobar',
 'provider': 'mysql',
 'database': 'demo',
 'table': 'friends'}
In [ ]:

</html>