Python Try to Insert Data Into Mysql Again

August 12, 2019

Tutorial: Inserting Records and DataFrames Into a SQL Database

Acquire to insert data into SQL databases similar a pro!

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

SQL (Structured Query Language) is based on Eastward. F. Codd'southward Relational model and algebra to manage the relational databases. It's a database query language used to create, insert, query, and manipulate the relational database and used by a big number of applications.

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

In this tutorial, we'll learn about SQL insertion operations in detail. Here is the list of topics that we volition larn 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() control
• Reading records from a database
• Updating records in a database

Want to attain a higher level of SQL skill? Sign up for free and check out Dataquest's SQL courses for thorough, interactive lessons on all the SQL skills y'all'll need for data science.

SQL Insertion

SQL Insertion is an essential functioning for data workers to sympathise. Inserting missing information or adding new information is a major function of the information cleaning procedure on nearly data science projects.

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

In fact, whether or non you lot're aware of information technology, information is flowing into databases using SQL inserts all the time! When y'all make full out a marketing survey, consummate a transaction, file a regime form online, or do any of thousands of other things, your information is likely being inserted into a database somewhere using SQL.

Permit'south dive into how we tin really use SQL to insert data into a database. We tin can insert data row by row, or add multiple rows at a time.

Inserting records into a database

In SQL, nosotros use the INSERT control to add records/rows into tabular array information. This command will not change the actual structure of the tabular array we're inserting to, it only adds information.

Let'southward imagine we have a data tabular array like the one below, which is beingness used to shop some information about a visitor's employees.

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

This employee table could exist created using the CREATE TABLE command, then we could apply that command to create an entirely new table. But it would be very inefficient to create a completely new table every time nosotros desire to add together information! Instead, permit's use the INSERT command to add the new data into our existing tabular array.

Hither's the basic syntax for using INSERT in SQL:

We start with the command INSERT INTO followed past the proper noun of table into which we'd like to insert data. After the table name, we list the columns of new data we're inserting column by column, inside parentheses. So, on the side by side line, we used the command VALUES forth with the values nosotros want to insert (in sequence within parentheses.

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

Inserting Records Into a Database From Python

Since we're frequently working with our data in Python when doing data science, let'due south insert information from Python into a MySQL database. This is a common chore that has a variety of applications in data scientific discipline.

We can send and receive information to a MySQL database by establishing a connection betwixt Python and MySQL. There are diverse ways to found this connection; here, we will use pymysql for database connectivity.

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

Let'southward walk through this process step by step.

Step i: Import the pymysql module.

            # Import pymysql module import pymysql          

Step two: Create connection a to the MySQL database

Create a connexion using pymysql's connect() role with the parameters host, user, database name, and countersign.

(The parameters beneath are for demonstration purposes only; y'all'll demand to fill up in the specific admission details required for the MySQL database yous're accessing.)

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

Stride 3: Create a cursor using the cursor() function.

This will permit us to execute the SQL query in one case we've written information technology.

            cursor = connection.cursor()          

Step 4: Execute the required SQL query

Commit the changes using the commit() function, and check the inserted records. Notation that we tin create a variable called sql, assign our query's syntax to it, and then pass sql and the specific information nosotros desire to insert as arguments to cursor.execute().

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

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

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

We can do this by querying the database for the entire contents of employee, and then fetching and press 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 use a for loop to print them one line at a time event = cursor.fetchall() for i in result:     print(i)          

            (1001, 'John', 2, 4000, 'IT', 'New Delhi')  (1002, 'Anna', 1, 3500, 'HR', 'Mumbai')  (1003, 'James', one, 2500, 'HR', 'Bombay')  (1004, 'David', 2, 5000, 'IT', 'New Delhi')  (1005, 'Marker', 2, 3000, 'Information technology', 'New Delhi')  (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai')  (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai')  (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')          

It worked! Above, we tin see the new record has been inserted and is now the last row in our MySQL database.

Stride five: Close the database connexion

Now that we're done, we should shut the database connectedness using shut() method.

            # Shut the connection connection.close()          

Of course, information technology would be better to write this code in a manner that could better handle exceptions and errors. Nosotros can practise this using try to comprise the body of our code and except to print errors if whatever ascend. So, nosotros can use finally to close the connection once we're finished, regardless of whether try succeeded or failed.

Here'due south what that looks like all together:

            import pymysql  try:     # Connect to the database     connection = pymysql.connect(host='localhost',                              user='root',                              countersign='12345',                              db='employee')      cursor=connection.cursor()      # Create a new tape     sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %south, %s)"     cursor.execute(sql, (1009,'Morgan',1,4000,'60 minutes','Mumbai'))      # connection 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     issue = cursor.fetchall()     for i in outcome:         print(i)  except Error as eastward:     print(e)  finally:     # shut the database connection using close() method.     connexion.close()          

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

Inserting Pandas DataFrames Into Databases Using INSERT

When working with data in Python, nosotros're frequently using pandas, and we've oftentimes got our data stored equally a pandas DataFrame. Thankfully, we don't need to do whatsoever conversions if we desire to apply SQL with our DataFrames; we can directly insert a pandas DataFrame into a MySQL database using INSERT.

Once once again, nosotros'll take it step-by-stride.

Step i: Create DataFrame using a dictionary

We could also import data from a CSV or create a DataFrame in whatsoever number of other ways, but for the purposes of this case, nosotros're just going to create a small 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'],     'price':[29, 23, 27] })  information          

	book_id	title	price
0	12345	Python Programming	29
i	12346	Larn MySQL	23
two	12347	Data Scientific discipline Cookbook	27

Step 2: Create a tabular array in our MySQL database

Before inserting information into MySQL, we're going to to create a volume table in MySQL to hold 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 name (in this example, book_details), and and then list each column and its corresponding datatype.

Step 3: Create a connection to the database

In one case we've created that table, we can in one case once more create a connection to the database from Python using pymysql.

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

Step 4: Create a cavalcade listing and insert rows

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

(It is as well possible to insert the entire DataFrame at in one case, and we'll look at a fashion of doing that in the next department, but first let's look at how to do information technology row-by-row).

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

Step 5: Query the database to check our work

Again, permit's 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 consequence:     print(i)          

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

Once we're satisfied that everything looks right, nosotros tin close the connection.

            connection.close()          

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

Now let's try to practise the same affair — insert a pandas DataFrame into a MySQL database — using a different technique. This fourth dimension, we'll use the module sqlalchemy to create our connexion and the to_sql() office to insert our data.

This approach accomplishes the same end issue in a more direct way, and allows us to add a whole dataframe to a MySQL database all at once.

            # Import modules import pandas as 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	title	price
0	12345	Python Programming	29
one	12346	Learn MySQL	23
2	12347	Information Scientific discipline 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",                                prisoner of war="12345",                                db="employee"))          

In one case we're continued, we can export the whole DataFrame to MySQL using the to_sql() function with the parameters table name, engine proper name, if_exists, and chunksize.

We'll take a closer expect at what each of these parameters refers to in a moment, but get-go, take a look at how much simpler information technology is to insert a pandas DataFrame into a MySQL database using this method. We can do information technology with simply a single line of code:

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

At present let'southward take a closer await at what each of these parameters is doing in our code.

• book_details is the proper name of tabular array into which we want to insert our DataFrame.
• con = engine provides the connection details (recall that we created engine using our authentication details in the previous footstep).
• if_exists = 'append' checks whether the tabular array we specified already exists or not, and then appends the new data (if it does exist) or creates a new tabular array (if it doesn't).
• chunksize writes records in batches of a given size at a time. By default, all rows volition be written at in one case.

Reading Records from a Database

Once we've used SQL inserts to become our information into the database, we'll want to be able to read it dorsum! So far in this tutorial, we've checked our SQL inserts by only printing the entire database, simply obviously this is not a viable choice with larger databases where you'd be printing thousands of rows (or more). Then allow'due south take a more in-depth look at how we can read dorsum the records we've created or inserted into our SQL database.

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

Hither's how the syntax for these commands looks:

We start with a SELECT clause, followed by list of columns, or * if we want to select all columns.And so we'll utilize a FROM clause to name the table nosotros'd like to look at. WHERE tin can exist used to filter the records and followed by a filter condition, and we can likewise utilize Society BY to sort the records. (The WHERE and Order By clauses are optional).

With larger databases, WHERE is useful for returning only the data nosotros want to encounter. So if, for example, we've just inserted some new data about a detail department, nosotros could use WHERE to specify the department ID in our query, and it would return but the records with a department ID that matches the i we specified.

Compare, for example, the results of these two queries using our employee table from earlier. In the get-go, we're returning all the rows. In the 2d, nosotros're getting dorsum only the rows we've asked for. This may non brand a big difference when our table has seven rows, but when you're working with seven g rows, or even vii million, using WHERE to return only the results y'all want is very of import!

If we want to practise this from inside Python, we tin use the same script nosotros used earlier in this tutorial to query these records. The only divergence is that we'll tell pymysql to execute the SELECT control rather than the INSERT control we used before.

            # Import module import pymysql  # create connectedness connectedness = pymysql.connect(host='localhost',                              user='root',                              countersign='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 result:     print(i)  # Close the connection connectedness.close()          

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

Above, we've selected and printed the unabridged database, but if we wanted to use WHERE to brand a more than conscientious, limited choice, the approach is the same:

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

Updating Records in the Database

Often, nosotros'll need to change the records in the table after creating them.

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

To alter existing records in the table, we need to use the UPDATE command. UPDATE is used to change the contents of existing records. We tin can specify specific columns and values to change using Fix, and we can also make provisional changes with WHERE to apply those changes only to rows that encounter that condition.

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

Exist conscientious — without the WHERE clause, this query would update every tape in the table, so don't forget that!

After executing the to a higher place query, the updated table would look like this:

Conclusion

In this tutorial, nosotros've taken a wait at SQL inserts and how to insert data into MySQL databases from Python. We too learned to insert Pandas DataFrames into SQL databases using 2 different 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 actually want to become a master of SQL, sign up for free and dive into one of Dataquest'southward interactive SQL courses to become interactive pedagogy and hands-on experience writing all the queries y'all'll demand to do productive, professional data science work.

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

• Do you need a SQL certification?
• SQL interview questions to prep for chore interviews
• Our SQL crook canvass

kilgocommill.blogspot.com

Source: https://www.dataquest.io/blog/sql-insert-tutorial/

Share this post