---
title: "How To Manipulate SQL Data Using SQLAlchemy and Pandas | CoderPad"
description: "Dealing with databases through Python is easily achieved using SQLAlchemy. Manipulating data through SQLAlchemy can be accomplished in most tasks, but there are some cases you need to integrate your database solution with the Pandas library. In this blog post, you'll learn how to manipulate SQL data using SQLAlchemy an"
canonical: "https://coderpad.io/blog/development/how-to-manipulate-sql-data-using-sqlalchemy-and-pandas/"
last_modified: "2026-04-15 16:20:59"
---
[Back to blog](https://coderpad.io/blog/)

# How To Manipulate SQL Data Using SQLAlchemy and Pandas

January 23, 2023 4 Minute Read

By EzzEddin Abdullah

[Development](https://coderpad.io/blog/development/)

Dealing with databases through Python is easily achieved using [SQLAlchemy](https://www.sqlalchemy.org/). Manipulating data through SQLAlchemy can be accomplished in most tasks, but there are some cases you need to integrate your database solution with the [Pandas](https://pandas.pydata.org/) library.

This is useful, especially if you’re comfortable manipulating data through Pandas. There are also cases when you have a large CSV file, and you want to import the contained data into your SQLAlchemy. You can also use Pandas with SQLAlchemy when you already have a [DataFrame](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html) that you want to import to your database instead of manual SQL inserts.

In this tutorial, you’ll learn how to import data from SQLAlchemy to a Pandas data frame, how to export Pandas data frame to SQLAlchemy, and how to load a CSV file into a database.

## Database setup with SQLAlchemy ORM

The following snippet contains a database setup that we will use in this tutorial:

```
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Text, ForeignKey
from sqlalchemy.engine import URL
from sqlalchemy.orm import declarative_base, sessionmaker
from datetime import datetime
import pandas as pd

Base = declarative_base()

class Article(Base):
    __tablename__ = 'articles'

    id = Column(Integer(), primary_key=True)
    slug = Column(String(100), nullable=False, unique=True)
    title = Column(String(100), nullable=False)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now,
    content = Column(Text)

url = URL.create(
    drivername="postgresql",
    username="coderpad",
    host="/tmp/postgresql/socket",
    database="coderpad"
    )

engine = create_engine(url)
Session = sessionmaker(bind=engine)
session = Session()Code language: Python (python)
```

>

![ℹ️](https://coderpad.io/assets/emoji/2139.svg) If you want to know the details of this setup and what every line entails, you can consult the previous [SQLAlchemy with PostgreSQL tutorial](https://coderpad.io/blog/development/sqlalchemy-with-postgresql/) that we discussed in detail.

Now, the session object is ready to be used to interact with the database. You can insert a new article and commit that change to the PostgreSQL engine:

```
article1 = Article(
    slug="clean-python",
    title="How to Write Clean Python",
    content="Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
    )

Base.metadata.create_all(engine)

session.add(article1)
session.commit()Code language: Python (python)
```

Here, we add the article object that has the title “How to Write Clean Python”.

## How to import data from SQLAlchemy to Pandas DataFrame

We want the article we inserted into our database to be imported to a Pandas data frame. To do that, we need to use the [read_sql()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html) Pandas method:

```
first_article_query = session.query(Article).filter(Article.id == 1)
first_article_df = pd.read_sql(
    sql=first_article_query.statement,
    con=engine
)

print(first_article_df['title'])Code language: Python (python)
```

So here, the`first_article_query`is a query object from SQLAlchemy retrieving the article with an`id`of`1`. We then pass this object to the`read_sql`method with the statement method applied to it. This statement method retrieves the SQLAlchemy selectable object that we need for the SQL parameter in the`read_sql`method.

We then pass the engine object to the`con`parameter of that method. Finally, we have our new Pandas data frame:`first_article_df`.

If you print the title, you’ll get the expected result:

```
>>> first_article_df['title']
0    How to Write Clean Python
Name: title, dtype: objectCode language: CSS (css)
```

>

![ℹ️](https://coderpad.io/assets/emoji/2139.svg) This`read_sql`method only reads SQL queries. It does not write data in the database. For example, if you want to change the title of that article:

```
first_article_df['title'] = 'Cleaner Python'Code language: Python (python)
```

You’ll only see that this value is changed in the data frame:

```
>>> first_article_df['title']
0    Cleaner Python
Name: title, dtype: objectCode language: CSS (css)
```

Not in the database:

```
>>> session.query(Article).filter(Article.id == 1).first().title
u'How to Write Clean Python'Code language: JavaScript (javascript)
```

## How to export Pandas DataFrame to SQLAlchemy

If you want to change the database through a`DataFrame`, you can use the [to_sql()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html) Pandas method. This method can write records stored in a`DataFrame`to a SQL database.

So here is a sample data frame that contains a new article:

```
new_article_df = pd.DataFrame(
    {
        'slug': ['sqlalchemy-pandas'],
        'title': ['SQLAlchemy Pandas Article'],
        'content': ['Bla bla bla'],
    })Code language: Python (python)
```

If you want to insert this new article into the database, you need to use the`to_sql()`method as follows:

```
new_article_df.to_sql(
    name='articles',
    con=engine,
    if_exists='append',
    index=False,
)Code language: Python (python)
```

Here, we pass the table name to the name argument and the engine object to the`con`argument.

We also specify the`append`option to the`if_exists`parameter to indicate that the table already exists and that we want to append that new article to the`articles`table. If you leave this parameter blank, it will automatically fail because the`if_exists`param is by default set to “fail”, and the articles table exists. However, if you set it to “replace”, the`articles`table will be dropped before inserting that new article.

You can make sure this article is already committed to the database by the following query statement:

```
session.query(Article).filter(
    Article.slug=='sqlalchemy-pandas'
).first().titleCode language: Python (python)
```

```
>>> session.query(Article).filter(Article.slug == 'sqlalchemy-pandas').first().title
u'SQLAlchemy Pandas Article'Code language: JavaScript (javascript)
```

## How to load a CSV file into a database

In some cases, you may have a large CSV file that you want to import into the database. Instead of having a data entry clerk who can insert each record one by one, you can grab all data in that file and insert it into the database.

You can do so by loading the CSV file to a data frame:

```
new_df = pd.read_csv("more_articles.csv")Code language: Python (python)
```

And then use the same`to_sql()`method to insert these records:

```
new_df.to_sql(
    name='articles',
    con=engine,
    if_exists='append',
    index=False,
)Code language: PHP (php)
```

That way, you have inserted new articles into your database. Just make sure that the column names exactly match the name of your columns. If this is not the case, you may use the [`rename()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rename.html) Pandas method on your data frame.

Try manipulating the SQL data in the sandbox below:

## Conclusion

This tutorial has covered how to interact with SQLAlchemy and Pandas libraries to manipulate data. We discussed how to import data from SQLAlchemy to Pandas`DataFrame`using`read_sql`, how to export Pandas`DataFrame`to the database using`to_sql`, and how to load a CSV file to get a`DataFrame`that can be shipped to the database.

I’m Ezz. I’m an AWS Certified Machine Learning Specialist and a Data Platform Engineer. I help SaaS companies rank on Google. Check out my [website](https://ezzeddinabdullah.com/) for more.

## Subscribe for more CoderPad insights

## Trending Articles

Previous slide Next slide [Development

## What is Prompt Engineering?

Introducing AI tools such as ChatGPT and Bard helps increase the efficiency of tasks such as data generation, code generation and completion, text summarization, etc. How were these AI tools trained to answer almost every question you ask them? The answer, prompt engineering, is discussed in this blog post.

Read the post](https://coderpad.io/blog/development/what-is-prompt-engineering/) [Development

## A Guide To Using React’s useCallback() Hook

Are you looking to optimize your React applications? Have you heard of the useCallback hook? useCallback is a powerful React hook that helps you optimize your application by preventing unnecessary re-renders. In this post, we'll dive into React's useCallback() hook, define referential equality and callback functions and how to tie it all together.

Read the post](https://coderpad.io/blog/development/a-guide-to-using-reacts-usecallback-hook/) [Development

## MySQL vs. PostgreSQL: How Do They Compare?

A database is a convenient tool that allows users to access information, maintain records, and apply commonly used operations, such as insertion, modification, deletion, and data organization. Learn about MySQL vs PostgreSQL, their features, advantages, and disadvantages. Finally, check out which one is the best choice for you.

Read the post](https://coderpad.io/blog/development/mysql-vs-postgresql-how-do-they-compare/) [Development

## PostgreSQL LIKE Operator: A Detailed Guide

When using databases, searching for values that match a given pattern is a familiar necessity. Learn how you can retrieve entries with queries including a particular string using Postgres LIKE operator.

Read the post](https://coderpad.io/blog/development/postgresql-like-operator-a-detailed-guide/) [Development

## How To Use DataProvider In TestNG With Examples

When it comes to testing there are many tools and techniques available. In this post, we'll discuss how to use DataProvider with TestNG.

Read the post](https://coderpad.io/blog/development/how-to-use-dataprovider-in-testng-with-examples/) [Development

## How To Get Up And Running With Django Migrations: A Guide

Django migrations are a way of handling the application of changes to a database schema. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects.

Read the post](https://coderpad.io/blog/development/how-to-get-up-and-running-with-django-migrations-a-guide/) [Development

## Writing A Parameterized Test In JUnit With Examples

A JUnit parameterized test allows you to decouple data from structure, generating cleaner and more reliable tests. Learn more in this guide.

Read the post](https://coderpad.io/blog/development/writing-a-parameterized-test-in-junit-with-examples/) [Development

## How To Use Assert In JUnit With Examples

The JUnit Assert class is what you use to write unit testing assertions in JUnit. Learn more about it in this guide, full of examples.

Read the post](https://coderpad.io/blog/development/how-to-use-assert-in-junit-with-examples/) [Development

## A Simple Guide To TypeORM: What It Is And How To Use It

Considering current languages have object-oriented programming features, most database management systems are based on the relational model, with tables instead of objects. For these reasons, mapping relational objects is very important in development. In this article, you will learn what TypeORM, one of the main tools used today, is.

Read the post](https://coderpad.io/blog/development/a-simple-guide-to-typeorm-what-it-is-and-how-to-use-it/) [Development

## Using PreparedStatement in JDBC With Examples

In this post, we'll see why and how to work with JDBC and PreparedStatement. We will also learn along with examples using code.

Read the post](https://coderpad.io/blog/development/using-preparedstatement-in-jdbc-with-examples/) [Development

## React vs React Native: The Differences Explained

React and React Native are similar in that they both allow web developers to build applications based on the unique concept of components. But the two frameworks do this in radically different ways. In this post, we cover the key differences between react and react native frameworks, implementation, and which is an appropriate choice.

Read the post](https://coderpad.io/blog/development/react-vs-react-native-the-differences-explained/) [Development

## Logging in Django

The most challenging part of fixing a problem is identifying it. Unfortunately, this statement is exceptionally true when it comes to debugging. Learn how to use the power of logging into your Django applications to improve your debug process in this article.

Read the post](https://coderpad.io/blog/development/logging-in-django/)

## Hire with confidence.

See why engineering teams trust CoderPad to evaluate developers with realistic, AI-ready hiring workflows.

[Request a demo](https://coderpad.io/sales/)