Creation | pd.read_sql_table()

Previous Next

Method:

pd.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None, dtype_backend=<no_default>)

Reads an entire SQL table into a DataFrame.

Returns:

pandas.core.frame.DataFrame

Parameters:

table_name: str-

Name of the SQL table to be read.

import sqlite3
import pandas as pd
from sqlalchemy import create_engine

# Create a SQLite database connection
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a sample table
cursor.execute('CREATE TABLE IF NOT EXISTS emps (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER, department TEXT)')

# Insert sample data into the table
cursor.executemany('INSERT INTO emps (name, salary, department) VALUES (?, ?, ?)', [('Alice', 75000, 'HR'), ('Bob', 80000, 'Engineering'), ('Chloe', 85000, 'Marketing')])
conn.commit()
conn.close()

# Create SQLAlchemy engine for pandas to use
engine = create_engine('sqlite:///example.db')

# NOTE: read_sql_table only works with SQLAlchemy engines, not sqlite3 directly
df = pd.read_sql_table(table_name='emps', con=engine)
print(df)
'''
Output:
   id   name  salary   department
0   1  Alice   75000           HR
1   2    Bob   80000  Engineering
2   3  Chloe   85000    Marketing
'''

con: (SQLAlchemy connectable or str)-

SQLAlchemy connection engine or database connection.

import sqlite3
import pandas as pd
from sqlalchemy import create_engine

# Create a SQLite database connection
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a sample table
cursor.execute('CREATE TABLE IF NOT EXISTS emps (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER, department TEXT)')

# Insert sample data into the table
cursor.executemany('INSERT INTO emps (name, salary, department) VALUES (?, ?, ?)', [('Alice', 75000, 'HR'), ('Bob', 80000, 'Engineering'), ('Chloe', 85000, 'Marketing')])
conn.commit()
conn.close()

# Create SQLAlchemy engine for pandas to use
engine = create_engine('sqlite:///example.db')

# NOTE: read_sql_table only works with SQLAlchemy engines, not sqlite3 directly
df = pd.read_sql_table(table_name='emps', con=engine)
print(df)
'''
Output:
   id   name  salary   department
0   1  Alice   75000           HR
1   2    Bob   80000  Engineering
2   3  Chloe   85000    Marketing
'''

schema: None, Optional-

Name of SQL schema in database to query (if database flavor supports this). Uses default schema if None (default).

index_col: str, Optional-

Column(s) to set as the DataFrame index.

import sqlite3
import pandas as pd
from sqlalchemy import create_engine

# Create a SQLite database connection
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a sample table
cursor.execute('CREATE TABLE IF NOT EXISTS eid (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER, department TEXT)')

# Insert sample data into the table
cursor.executemany('INSERT INTO eid (name, salary, department) VALUES (?, ?, ?)', [('Alice', 75000, 'HR'), ('Bob', 80000, 'Engineering'), ('Chloe', 85000, 'Marketing')])
conn.commit()
conn.close()

# Create SQLAlchemy engine for pandas to use
engine = create_engine('sqlite:///example.db')

# NOTE: read_sql_table only works with SQLAlchemy engines, not sqlite3 directly
df = pd.read_sql_table(table_name='eid', con=engine, index_col='id')
print(df)
'''
Output:
     name  salary   department
id                            
1   Alice   75000           HR
2     Bob   80000  Engineering
3   Chloe   85000    Marketing
'''

coerce_float: bool, Optional-

It is used to control whether non-numeric data that can be interpreted as numeric values should be converted to float type when reading data from the SQL table into a DataFrame.

parse_dates: None, Optional-

Columns to parse as dates.

import sqlite3
import pandas as pd
from sqlalchemy import create_engine

# Create a SQLite database connection
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a sample table
cursor.execute('CREATE TABLE IF NOT EXISTS pbn (id INTEGER PRIMARY KEY, name TEXT, salary TEXT, date TEXT)')

# Insert sample data into the table
cursor.executemany('INSERT INTO pbn (name, salary, date) VALUES (?, ?, ?)', [('Alice', "75000.50", '2003-10-02'), ('Bob', "80000", '2003-04-16'), ('Chloe', "85000", '2023-12-29')])
conn.commit()
conn.close()

# Create SQLAlchemy engine for pandas to use
engine = create_engine('sqlite:///example.db')

# NOTE: read_sql_table only works with SQLAlchemy engines, not sqlite3 directly
df = pd.read_sql_table(table_name='pbn', con=engine, parse_dates=["date"])
print(df['date'])
'''
Output:
0   2003-10-02
1   2003-04-16
2   2023-12-29
Name: date, dtype: datetime64[ns]
'''

columns: None, Optional-

List of specific columns to read.

import sqlite3
import pandas as pd
from sqlalchemy import create_engine

# Create a SQLite database connection
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a sample table
cursor.execute('CREATE TABLE IF NOT EXISTS pcol (id INTEGER PRIMARY KEY, name TEXT, salary TEXT, date TEXT)')

# Insert sample data into the table
cursor.executemany('INSERT INTO pcol (name, salary, date) VALUES (?, ?, ?)', [('Alice', "75000.50", '2003-10-02'), ('Bob', "80000", '2003-04-16'), ('Chloe', "85000", '2023-12-29')])
conn.commit()
conn.close()

# Create SQLAlchemy engine for pandas to use
engine = create_engine('sqlite:///example.db')

# NOTE: read_sql_table only works with SQLAlchemy engines, not sqlite3 directly
df = pd.read_sql_table(table_name='pcol', con=engine, columns=["id", "name"])
print(df)
'''
Output:
   id   name
0   1  Alice
1   2    Bob
2   3  Chloe
'''

chunksize: None, Optional-

Number of rows per chunk for iteration.

import sqlite3
import pandas as pd
from sqlalchemy import create_engine

# Create a SQLite database connection
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# Create a sample table
cursor.execute('CREATE TABLE IF NOT EXISTS pch (id INTEGER PRIMARY KEY, name TEXT, salary TEXT, date TEXT)')

# Insert sample data into the table
cursor.executemany('INSERT INTO pch (name, salary, date) VALUES (?, ?, ?)', [('Alice', "75000.50", '2003-10-02'), ('Bob', "80000", '2003-04-16'), ('Chloe', "85000", '2023-12-29')])
conn.commit()
conn.close()

# Create SQLAlchemy engine for pandas to use
engine = create_engine('sqlite:///test.db')

# NOTE: read_sql_table only works with SQLAlchemy engines, not sqlite3 directly
df = pd.read_sql_table(table_name='pch', con=engine, chunksize=2)
print(next(df))
'''
Output:
   id   name    salary        date
0   1  Alice  75000.50  2003-10-02
1   2    Bob     80000  2003-04-16
'''

dtype_backend: None, Optional-

The dtype_backend parameter is new in Pandas 2.0 which is used to specify the backend for handling the types of data when reading a file.

Previous Next

BetterDocs

Support

EmailDiscordForms

Documentations

Python

Company

AboutDocs

Policies

Terms of ServicePrivacy Policy