python-pandas and databases like mysql

Question

The documentation for Pandas has numerous examples of best practices for working with data stored in various formats   However  I am unable to find any good examples for working with databases like MySQL for example   Can anyone point me to links or give some code snippets of how to convert query results using mysql-python to data frames in Pandas efficiently

User · Answer

For recent readers of this question  pandas have the following warning in their docs for version 14 0      Warning  Some of the existing functions or function aliases have been   deprecated and will be removed in future versions  This includes    tquery  uquery  read frame  frame query  write frame     And      Warning  The support for the    mysql    flavor when using DBAPI connection objects has   been deprecated  MySQL will be further supported with SQLAlchemy   engines  GH6900     This makes many of the answers here outdated  You should use sqlalchemy   from sqlalchemy import create engine import pandas as pd engine   create engine  dialect   user pass host port schema   echo False  f   pd read sql query  SELECT   FROM mytable   engine  index col    ID

User · Answer

import the module  import pandas as pd import oursql   connect  conn oursql connect host  localhost  user  me  passwd  mypassword  db  classicmodels   sql  Select customerName  city country from customers order by customerName country city  df mysql   pd read sql sql conn  print df mysql   That works just fine and using pandas io sql frame works  with the deprecation warning   Database used is the sample database from mysql tutorial

User · Answer

For Postgres users  import psycopg2 import pandas as pd  conn   psycopg2 connect  database  datawarehouse  user  user1  host  localhost  password  uberdba     customers    select   from customers   customers df   pd read sql customers conn   customers df

User · Answer

This helped for me for connecting to AWS MYSQL RDS  from python 3 x based lambda function and loading into a pandas DataFrame  import json import boto3 import pymysql import pandas as pd user    username  password    XXXXXXX  client   boto3 client  rds   def lambda handler event  context       conn   pymysql connect host  xxx xxxxus-west-2 rds amazonaws com   port 3306  user user  passwd password  db  database name   connect timeout 5      df  pd read sql  select   from TableName limit 10  con conn      print df        TODO implement      return             statusCode   200            df   df

User · Answer

MySQL example   import MySQLdb as db from pandas import DataFrame from pandas io sql import frame query  database   db connect  localhost   username   password   database   data       frame query  SELECT   FROM data   database

User · Answer

And this is how you connect to PostgreSQL using psycopg2 driver  install with  apt-get install python-psycopg2  if you re on Debian Linux derivative OS    import pandas io sql as psql import psycopg2  conn   psycopg2 connect  dbname  datawarehouse  user  user1  host  localhost  password  uberdba     q      select month idx  sum payment  from bi some table     df3   psql frame query q  conn

User · Answer

As Wes says  io sql s read sql will do it  once you ve gotten a database connection using a DBI compatible library   We can look at two short examples using the MySQLdb and cx Oracle libraries to connect to Oracle and MySQL and query their data dictionaries  Here is the example for cx Oracle   import pandas as pd import cx Oracle  ora conn   cx Oracle connect  your connection string   df ora   pd read sql  select   from user objects   con ora conn      print  loaded dataframe from Oracle    Records     len df ora  ora conn close     And here is the equivalent example for MySQLdb   import MySQLdb mysql cn  MySQLdb connect host  myhost                    port 3306 user  myusername   passwd  mypassword                    db  information schema   df mysql   pd read sql  select   from VIEWS    con mysql cn      print  loaded dataframe from MySQL  records    len df mysql  mysql cn close

User · Answer

This should work just fine   import MySQLdb as mdb import pandas as pd con   mdb connect    127 0 0 1        root        password        database name      with con   cur   con cursor    cur execute    select random number one  random number two  random number three from randomness a random table      rows   cur fetchall    df   pd DataFrame    ij for ij in i  for i in rows     df rename columns  0     Random Number One     1     Random Number Two     2     Random Number Three      inplace True    print df head 20

User · Answer

The same syntax works for Ms SQL server using podbc also    import pyodbc import pandas io sql as psql  cnxn   pyodbc connect  DRIVER  SQL Server  SERVER servername DATABASE mydb UID username PWD password    cursor   cnxn cursor   sql       select   from mytable      df   psql frame query sql  cnxn  cnxn close

User · Answer

For Sybase the following works  with http   python-sybase sourceforge net   import pandas io sql as psql import Sybase  df   psql frame query   lt Query gt    con Sybase connect   lt dsn gt      lt user gt      lt pwd gt

User · Answer

pandas io sql frame query is deprecated  Use pandas read sql instead

User · Answer

For the record  here is an example using a sqlite database   import pandas as pd import sqlite3  with sqlite3 connect  whatever sqlite   as con      sql    SELECT   FROM table name      df   pd read sql query sql  con      print df shape

User · Answer

I prefer to create queries with SQLAlchemy  and then make a DataFrame from it  SQLAlchemy makes it easier to combine SQL conditions Pythonically if you intend to mix and match things over and over   from sqlalchemy ext declarative import declarative base from sqlalchemy import Table from sqlalchemy import create engine from sqlalchemy orm import sessionmaker from pandas import DataFrame import datetime    We are connecting to an existing service engine   create engine  dialect   user pwd host port db   echo False  Session   sessionmaker bind engine  session   Session   Base   declarative base      And we want to query an existing table tablename   Table  tablename        Base metadata       autoload True       autoload with engine       schema  ownername      These are the  Where  parameters  but I could as easily    create joins and limit results us   tablename c country code in    US   MX    dc   tablename c locn name like   DC    dt   tablename c arr date  gt   datetime date today     Give me convenience or     q   session query tablename                filter us  amp  dc  amp  dt    That s where the magic happens     def querydb query               Function to execute query and return DataFrame              df   DataFrame query all         df columns    x  name   for x in query column descriptions      return df  querydb q

[python] python-pandas and databases like mysql

Examples related to python

Examples related to pandas