Which Sql Statement Is Used To Insert New Data In A Database?

August 12, 2019

Tutorial: Inserting Records and DataFrames Into a SQL Database

Learn to insert data into SQL databases like a pro!

One of the cardinal roles of a information scientist is to extract patterns and insights from raw data. Since much of the world's government and corporate data is organized in relational databases, information technology makes sense that data scientists need to know how to work with these database structures. Writing SQL queries to insert, excerpt, and filter data in databases is a key skill for anyone interested in information analytics or data science.

SQL (Structured Query Linguistic communication) is based on Eastward. F. Codd's Relational model and algebra to manage the relational databases. It'due south a database query language used to create, insert, query, and dispense the relational database and used by a large number of applications.

Although it has been effectually for decades, learning SQL is withal a critical skill for modern data scientists, and really anyone who works with information at all, because SQL is used in all kinds of relational database software, including MySQL, SQL Server, Oracle, and PostgreSQL.

In this tutorial, nosotros'll learn about SQL insertion operations in detail. Here is the list of topics that we will learn in this tutorial:

SQL Insertion
Inserting records into a database
Inserting Pandas DataFrames into a database using the insert command
Inserting Pandas DataFrames into a database using the to_sql() command
Reading records from a database
Updating records in a database

Desire to reach a higher level of SQL skill? Sign up for complimentary and check out Dataquest's SQL courses for thorough, interactive lessons on all the SQL skills you'll need for data science.

SQL Insertion

SQL Insertion is an essential operation for information workers to understand. Inserting missing data or calculation new data is a major office of the information cleaning process on virtually data science projects.

Insertion is also how most data gets into databases in the first identify, so it's important anytime you lot're collecting information, as well. When your company gets new data on a customer, for example, chances are than a SQL insert will exist how that data gets into your existing client database.

In fact, whether or not yous're aware of it, data is flowing into databases using SQL inserts all the time! When you lot fill out a marketing survey, complete a transaction, file a government form online, or do any of thousands of other things, your information is likely being inserted into a database somewhere using SQL.

Allow's dive into how we can actually use SQL to insert information into a database. We tin can insert information row by row, or add multiple rows at a fourth dimension.

Inserting records into a database

In SQL, we apply the INSERT command to add together records/rows into table data. This command will not modify the actual construction of the table we're inserting to, information technology only adds data.

Let's imagine nosotros accept a data tabular array like the one below, which is being used to shop some information near a company's employees.

Now, allow's imagine we have new employees we need to put into the system.

This employee table could be created using the CREATE TABLE command, then we could use that command to create an entirely new tabular array. Merely it would be very inefficient to create a completely new tabular array every fourth dimension we want to add data! Instead, permit's use the INSERT command to add the new data into our existing table.

Here'southward the basic syntax for using INSERT in SQL:

We get-go with the command INSERT INTO followed by the proper name of table into which nosotros'd like to insert data. After the table proper noun, we list the columns of new information we're inserting cavalcade by column, within parentheses. Then, on the next line, nosotros used the command VALUES along with the values nosotros want to insert (in sequence inside parentheses.

So for our employee table, if we were adding a new employee named Kabir, our INSERT command might look like this:

Inserting Records Into a Database From Python

Since nosotros're often working with our information in Python when doing data science, let's insert data from Python into a MySQL database. This is a common job that has a diversity of applications in information science.

We can transport and receive data to a MySQL database by establishing a connection between Python and MySQL. In that location are various ways to establish this connection; hither, nosotros will use pymysql for database connectivity.

Hither are the broad steps we'll need to work through to get pymysql connected, insert our data, and and then extract the data from MySQL:

Let'south walk through this procedure pace by step.

Stride one: Import the pymysql module.

            # Import pymysql module import pymysql

Pace 2: Create connection a to the MySQL database

Create a connection using pymysql's connect() function with the parameters host, user, database proper noun, and password.

(The parameters below are for demonstration purposes simply; y'all'll need to fill in the specific access details required for the MySQL database yous're accessing.)

            # Connect to the database connection = pymysql.connect(host='localhost',                              user='root',                              password='12345',                              db='employee')

Step iii: Create a cursor using the cursor() function.

This will allow us to execute the SQL query once nosotros've written information technology.

            cursor = connection.cursor()

Step 4: Execute the required SQL query

Commit the changes using the commit() part, and cheque the inserted records. Note that nosotros can create a variable called sql, assign our query's syntax to it, and then laissez passer sql and the specific data we want to insert as arguments to cursor.execute().

Then, we'll commit these changes using commit().

            # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Bacon`, `Dname`, `Dlocation`) VALUES (%s, %s, %due south, %southward, %s, %s)"  # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi'))  # the connectedness is not autocommited by default. So we must commit to relieve our changes. connection.commit()

Let's do a quick check to see if the record we wanted to insert has actually been inserted.

We can do this by querying the database for the entire contents of employee, and and so fetching and printing those results.

            # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql)  # Fetch all the records and utilize a for loop to impress them i line at a time result = cursor.fetchall() for i in result:     impress(i)

            (1001, 'John', two, 4000, 'It', 'New Delhi')  (1002, 'Anna', 1, 3500, 'HR', 'Mumbai')  (1003, 'James', ane, 2500, 'Hr', 'Bombay')  (1004, 'David', 2, 5000, 'IT', 'New Delhi')  (1005, 'Mark', 2, 3000, 'IT', 'New Delhi')  (1006, 'Steve', three, 4500, 'Finance', 'Mumbai')  (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai')  (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')

It worked! Above, nosotros can see the new record has been inserted and is now the final row in our MySQL database.

Step five: Shut the database connection

Now that we're washed, we should close the database connexion using close() method.

            # Close the connexion connexion.close()

Of course, it would be better to write this code in a fashion that could amend handle exceptions and errors. We can do this using try to contain the body of our lawmaking and except to impress errors if any ascend. Then, we tin can use finally to close the connection once we're finished, regardless of whether endeavor succeeded or failed.

Here's what that looks like all together:

            import pymysql  endeavor:     # Connect to the database     connexion = pymysql.connect(host='localhost',                              user='root',                              countersign='12345',                              db='employee')      cursor=connection.cursor()      # Create a new record     sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Bacon`, `Dname`, `Dlocation`) VALUES (%southward, %due south, %s, %s, %s, %s)"     cursor.execute(sql, (1009,'Morgan',i,4000,'HR','Mumbai'))      # connectedness is not autocommit by default. So we must commit to save our changes.     connection.commit()      # Execute query     sql = "SELECT * FROM `employee`"     cursor.execute(sql)     # Fetch all the records     result = cursor.fetchall()     for i in result:         print(i)  except Error as e:     print(east)  finally:     # close the database connection using close() method.     connection.shut()

            ((1001, 'John', 2, 4000, 'Information technology', 'New Delhi'), (1002, 'Anna', 1, 3500, 'Hour', 'Mumbai'), (1003, 'James', one, 2500, 'Hr', 'Mumbai'), (1004, 'David', 2, 5000, 'It', 'New Delhi'), (1005, 'Mark', 2, 3000, 'IT', 'New Delhi'), (1006, 'Steve', 3, 4500, 'Finance', 'Bombay'), (1007, 'Alice', iii, 3500, 'Finance', 'Mumbai'), (1008, 'Kabir', ii, 5000, 'It', 'New Delhi'), (1009, 'Morgan', i, 4000, 'Hour', 'Mumbai'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'))

Inserting Pandas DataFrames Into Databases Using INSERT

When working with data in Python, nosotros're ofttimes using pandas, and we've often got our data stored every bit a pandas DataFrame. Thankfully, we don't demand to practise any conversions if we want to utilize SQL with our DataFrames; we can direct insert a pandas DataFrame into a MySQL database using INSERT.

Once again, we'll take it step-by-footstep.

Step 1: Create DataFrame using a lexicon

We could also import information from a CSV or create a DataFrame in whatsoever number of other ways, but for the purposes of this example, we're simply going to create a pocket-size DataFrame that saves the titles and prices of some data science texbooks.

            # Import pandas import pandas every bit pd  # Create dataframe data = pd.DataFrame({     'book_id':[12345, 12346, 12347],     'title':['Python Programming', 'Learn MySQL', 'Data Science Cookbook'],     'cost':[29, 23, 27] })  data

	book_id	title	price
0	12345	Python Programming	29
1	12346	Acquire MySQL	23
two	12347	Information Science Cookbook	27

Footstep 2: Create a table in our MySQL database

Before inserting data into MySQL, we're going to to create a book table in MySQL to concord our data. If such a table already existed, we could skip this step.

We'll utilize a CREATE TABLE statement to create our table, follow that with our table proper noun (in this example, book_details), and and then list each cavalcade and its corresponding datatype.

syntax for creating a table in SQL

Stride 3: Create a connectedness to the database

Once we've created that tabular array, we can in one case again create a connection to the database from Python using pymysql.

            import pymysql  # Connect to the database connection = pymysql.connect(host='localhost',                          user='root',                          countersign='12345',                          db='volume')  # create cursor cursor=connection.cursor()

Stride four: Create a column listing and insert rows

Adjacent, we'll create a column list and insert our dataframe rows one by one into the database by iterating through each row and using INSERT INTO to insert that row's values into the database.

(It is also possible to insert the entire DataFrame at one time, and nosotros'll look at a way of doing that in the next section, only get-go let's look at how to do it row-by-row).

            # creating column listing for insertion cols = "`,`".join([str(i) for i in data.columns.tolist()])  # Insert DataFrame recrds ane by one. for i,row in information.iterrows():     sql = "INSERT INTO `book_details` (`" +cols + "`) VALUES (" + "%due south,"*(len(row)-one) + "%s)"     cursor.execute(sql, tuple(row))      # the connection is not autocommitted past default, so we must commit to relieve our changes     connection.commit()

Step 5: Query the database to bank check our work

Over again, let'south query the database to make sure that our inserted data has been saved correctly.

            # Execute query sql = "SELECT * FROM `book_details`" cursor.execute(sql)  # Fetch all the records result = cursor.fetchall() for i in effect:     print(i)

            (12345, 'Python Programming', 29)  (12346, 'Acquire MySQL', 23)  (12347, 'Data Science Cookbook', 27)

One time nosotros're satisfied that everything looks correct, nosotros tin can shut the connexion.

            connectedness.close()

Inserting Pandas DataFrames into a Database Using the to_sql() Function

Now permit'south try to practise the same thing — insert a pandas DataFrame into a MySQL database — using a dissimilar technique. This fourth dimension, nosotros'll utilise the module sqlalchemy to create our connection and the to_sql() part to insert our data.

This approach accomplishes the aforementioned stop result in a more straight way, and allows us to add a whole dataframe to a MySQL database all at once.

            # Import modules import pandas every bit pd  # Create dataframe data=pd.DataFrame({     'book_id':[12345,12346,12347],     'title':['Python Programming','Learn MySQL','Data Science Cookbook'],     'price':[29,23,27] })  data

	book_id	championship	price
0	12345	Python Programming	29
1	12346	Learn MySQL	23
2	12347	Information Science Cookbook	27

Import the module sqlalchemy and create an engine with the parameters user, password, and database proper name. This is how we connect and log in to the MySQL database.

            # import the module from sqlalchemy import create_engine  # create sqlalchemy engine engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}"                        .format(user="root",                                pw="12345",                                db="employee"))

Once we're connected, we can export the whole DataFrame to MySQL using the to_sql() function with the parameters tabular array proper name, engine proper name, if_exists, and chunksize.

Nosotros'll have a closer look at what each of these parameters refers to in a moment, but commencement, accept a look at how much simpler it is to insert a pandas DataFrame into a MySQL database using this method. We tin can do it with only a single line of code:

            # Insert whole DataFrame into MySQL data.to_sql('book_details', con = engine, if_exists = 'append', chunksize = 1000)

Now let's have a closer look at what each of these parameters is doing in our code.

book_details is the name of table into which we desire to insert our DataFrame.
con = engine provides the connection details (recall that we created engine using our authentication details in the previous step).
if_exists = 'append' checks whether the table we specified already exists or not, and so appends the new information (if it does exist) or creates a new table (if it doesn't).
chunksize writes records in batches of a given size at a time. Past default, all rows will be written at once.

Reading Records from a Database

Once we've used SQL inserts to go our data into the database, we'll want to exist able to read information technology dorsum! So far in this tutorial, we've checked our SQL inserts by simply printing the entire database, only obviously this is not a viable pick with larger databases where you'd be printing thousands of rows (or more). So let's take a more in-depth expect at how we can read back the records nosotros've created or inserted into our SQL database.

We can read records from a SQL database using the SELECT command. We can select specific columns, or use * to select everything from a given table. We tin also select to return simply records that run into a item status using the WHERE control.

Here'southward how the syntax for these commands looks:

reading-records-syntax-in-sql

Nosotros starting time with a SELECT clause, followed past list of columns, or * if we want to select all columns.Then we'll utilise a FROM clause to name the table we'd like to look at. WHERE can be used to filter the records and followed by a filter condition, and nosotros can also use Lodge By to sort the records. (The WHERE and ORDER By clauses are optional).

With larger databases, WHERE is useful for returning only the data we want to run across. So if, for instance, we've just inserted some new information about a particular department, we could utilise WHERE to specify the department ID in our query, and it would return only the records with a department ID that matches the one we specified.

Compare, for example, the results of these two queries using our employee tabular array from before. In the first, we're returning all the rows. In the second, we're getting back only the rows we've asked for. This may non make a big difference when our tabular array has seven rows, simply when you're working with 7 g rows, or even seven million, using WHERE to return but the results you lot desire is very important!

If we want to do this from inside Python, nosotros can use the aforementioned script we used before in this tutorial to query these records. The simply difference is that we'll tell pymysql to execute the SELECT command rather than the INSERT control we used before.

            # Import module import pymysql  # create connectedness connection = pymysql.connect(host='localhost',                              user='root',                              password='12345',                              db='employee')  # Create cursor my_cursor = connection.cursor()  # Execute Query my_cursor.execute("SELECT * from employee")  # Fetch the records result = my_cursor.fetchall()  for i in outcome:     print(i)  # Close the connexion connection.close()

            (1001, 'John', 2, 4000, 'It', 'New Delhi')  (1002, 'Anna', 1, 3500, 'HR', 'Bombay')  (1003, 'James', 1, 2500, 'Hour', 'Mumbai')  (1004, 'David', two, 5000, 'Information technology', 'New Delhi')  (1005, 'Marking', 2, 3000, 'It', 'New Delhi')  (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai')  (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai')  (1008, 'Kabir', 2, 5000, 'It', 'New Delhi')  (1009, 'Morgan', 1, 4000, '60 minutes', 'Mumbai')  (1009, 'Morgan', 1, 4000, '60 minutes', 'Mumbai')

Above, we've selected and printed the entire database, merely if we wanted to use WHERE to brand a more careful, limited selection, the arroyo is the same:

            my_cursor.execute("SELECT * FROM employee WHERE DeptID=2")

Updating Records in the Database

Often, nosotros'll need to modify the records in the tabular array after creating them.

For example, imagine that an employee in our employee table got a promotion. We'd desire to update their salary data. The INSERT INTO command won't assist u.s. here, considering we don't want to add an entirely new row.

To modify existing records in the table, we demand to use the UPDATE command. UPDATE is used to modify the contents of existing records. Nosotros can specify specific columns and values to change using SET, and we tin also make conditional changes with WHERE to apply those changes only to rows that come across that status.

update table syntax

Now, let's update the records from our employee tabular array and display the results. In this case, let'southward say David got the promotion — we'll write a query using UPDATE that sets Salary to 6000 just in columns where the employee ID is 1004 (David's ID).

Be careful — without the WHERE clause, this query would update every record in the table, and so don't forget that!

After executing the above query, the updated tabular array would look similar this:

Decision

In this tutorial, we've taken a look at SQL inserts and how to insert data into MySQL databases from Python. We also learned to insert Pandas DataFrames into SQL databases using two dissimilar methods, including the highly efficient to_sql() method.

Of course, this is just the tip of the iceberg when information technology comes to SQL queries. If you lot really desire to go a principal of SQL, sign up for free and swoop into 1 of Dataquest's interactive SQL courses to become interactive instruction and hands-on experience writing all the queries you'll need to do productive, professional person data science work.

Also check out some of our other complimentary SQL-related resources:

Do you demand a SQL certification?
SQL interview questions to prep for job interviews
Our SQL crook canvass