Accessing databases from Pandas

We can give Pandas a database connection, such as the one in the previous example, or an SQLAlchemy connection. We will cover the latter in the later sections of this chapter. We will load the statsmodels sunactivity data, just as we did 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...