Book Image

Python Data Analysis

By : Ivan Idris
Book Image

Python Data Analysis

By: Ivan Idris

Overview of this book

Table of Contents (22 chapters)
Python Data Analysis
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Key Concepts
Online Resources
Index

Accessing databases from pandas


We can give pandas a database connection such as the one in the previous example or a SQLAlchemy connection. We will cover the latter in the later sections of this chapter. We will load the statsmodels sunactivity data, just like in the previous chapter, Chapter 7, Signal Processing and Time Series:

  1. Create a list of tuples to form the pandas DataFrame:

    rows = [tuple(x) for x in df.values]

    Contrary to the previous example, create a table without specifying data types:

    con.execute("CREATE TABLE sunspots(year, sunactivity)")
  2. The executemany() method executes multiple statements; in this case, we will be inserting records from a list of tuples. Insert all the rows into the table and show the row count as follows:

    con.executemany("INSERT INTO sunspots(year, sunactivity) VALUES (?, ?)", rows)
    c.execute("SELECT COUNT(*) FROM sunspots")
    print c.fetchone()

    The number of rows in the table is printed as follows:

    (309,)
    
  3. The rowcount attribute of the result of an execute() call...