How to Insert Values into Table through MySQLdb Python

So here I was trying to store the values of a csv file 'employee_reviews.csv' into my employees table. I'm reading through the file, storing it as a string, then splitting the elements all the way down to a 2D list which is then looped through each row. I'm pretty sure there is a more simple way of doing this. When I run my code, I get an Error ->

So here I was trying to store the values of a csv file 'employee_reviews.csv' into my employees table. I'm reading through the file, storing it as a string, then splitting the elements all the way down to a 2D list which is then looped through each row. I'm pretty sure there is a more simple way of doing this. When I run my code, I get an Error ->

cur.execute("INSERT INTO (id, company, location, dates, job_title, summary, pros, cons, advice_to_management, overall_ratings, work_balance_stars, culture_value_stars, career_opportunities_stars, comp_benefit_stars, senior_management_stars, helpful_count, link) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",(int(row[0]), row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], int(row[9]), row[10], row[11], row[12], row[13], row[14], int(row[15]), row[16] )) ValueError: invalid literal for int() with base 10: 'none'


import csv
import MySQLdb

mydb = MySQL.connect(host="host",user="user",passwd="passwd",db="Employee_Review")
cur = mydb.cursor()
#cur.execute("CREATE TABLE employees(id INT, company VARCHAR(20), location VARCHAR(40), dates DATE, job_title VARCHAR(20), summary VARCHAR(50), pros VARCHAR(1000), cons VARCHAR(1000), advice_to_management VARCHAR(50), overall_ratings INT, work_balance_stars VARCHAR(10), culture_value_stars VARCHAR(10), career_opportunities_stars VARCHAR(10), comp_benefit_stars VARCHAR(10), senior_management_stars VARCHAR(10), helpful_count INT, link VARCHAR(75) )")

f = open("employee_reviews.csv","r")
fString = f.read()

fList = []
for line in fString.split('\n'):
fList.append(line.split(','))

for row in fList:
if row == fList[0]:
continue #skip the first row for it contains the name of the columns

cur.execute("INSERT INTO (id, company, location, dates, job_title, summary, pros, cons, advice_to_management, overall_ratings, work_balance_stars, culture_value_stars, career_opportunities_stars, comp_benefit_stars, senior_management_stars, helpful_count, link) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",(int(row[0]), row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], int(row[9]), row[10], row[11], row[12], row[13], row[14], int(row[15]), row[16] ))
mydb.commit()

mydb.close()

The csv file can be found here: https://inclass.kaggle.com/petersunga/google-amazon-facebook-employee-reviews/discussion

What are the differences between Standard SQL and Transact-SQL?

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

#1 Names of Database Objects

In relational database systems, we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In standard SQL, you can place this kind of name in quotation marks (""), but in T-SQL, you can also place it in brackets ([]). Look at these examples for the name of a table in T-SQL:

CREATE TABLE dbo.test.“first name” ( Id INT, Name VARCHAR(100));
CREATE TABLE dbo.test.[first name]  ( Id INT, Name VARCHAR(100));

Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard.

What Is Different in a SELECT Statement?#2 Returning Values

The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. In T-SQL, it looks like the example below:

SELECT 12/6 ;

In this expression, we don’t need a table to evaluate 12 divided by 6, therefore, the FROM statement and the name of the table can be omitted.

#3 Limiting Records in a Result Set

In the SQL standard, you can limit the number of records in the results by using the syntax illustrated below:

SELECT * FROM tab FETCH FIRST 10 ROWS ONLY

T-SQL implements this syntax in a different way. The example below shows the MS SQL Server syntax:

SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

As you notice, this uses an ORDER BY clause. Another way to select rows, but without ORDER BY, is by using the TOP clause in T-SQL:

SELECT TOP 10 * FROM tab;
#4 Automatically Generating Values

The SQL standard enables you to create columns with automatically generated values. The syntax to do this is shown below:

CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);

In T-SQL we can also automatically generate values, but in this way:

CREATE TABLE tab (id INTEGER IDENTITY);
#5 Math Functions

Several common mathematical functions are part of the SQL standard. One of these math functions is CEIL(x), which we don’t find in T-SQL. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,[,d]) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL, you use the GREATEST(list) and LEAST(list) functions.

T-SQL function ROUND:

SELECT ROUND(col) FROM tab;

#6 Aggregate Functions

We find another syntax difference with the aggregate functions. The functions COUNT, SUM, and AVG all take an argument related to a count. T-SQL allows the use of DISTINCT before these argument values so that rows are counted only if the values are different from other rows. The SQL standard doesn't allow for the use of DISTINCT in these functions.

Standard SQL:
SELECT COUNT(col) FROM tab;

T-SQL:
SELECT COUNT(col) FROM tab;

SELECT COUNT(DISTINCT col) FROM tab;

But in T-SQL we don’t find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard.

#7 Retrieving Parts of Dates and Times

Most relational database systems deliver many functions to operate on dates and times.

In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x).

There is also a difference in getting the current date and time. Standard SQL allows you to get the current date with the CURRENT_DATE function, but in MS SQL Server, there is not a similar function, so we have to use the GETDATE function as an argument in the CAST function to convert to a DATE data type.

#8 Operating on Strings

Using functions to operate on strings is also different between the SQL standard and T-SQL. The main difference is found in removing trailing and leading spaces from a string. In standard SQL, there is the TRIM function, but in T-SQL, there are several related functions: TRIM (removing trailing and leading spaces), LTRIM (removing leading spaces), and RTRIM (removing trailing spaces).

Another very-often-used string function is SUBSTRING.

The standard SQL syntax for the SUBSTRING function looks like:

SUBSTRING(str FROM start [FOR len])

but in T-SQL, the syntax of this function looks like:

SUBSTRING(str, start, length)

There are reasons sometimes to add values coming from other columns and/or additional strings. Standard SQL enables the following syntax to do this:

As you can see, this syntax makes use of the || operator to add one string to another.

But the equivalent operator in T-SQL is the plus sign character. Look at this example:

SELECT col1 + col2  FROM tab;

In SQL Server, we also have the possibility to use the CONCAT function concatenates a list of strings:

SELECT CONCAT(col1, str1, col2, ...)  FROM tab;

We can also repeat one character several times. Standard SQL defines the function REPEAT(str, n) to do this. Transact-SQL provides the REPLICATE function. For example:

SELECT  REPLICATE(str, x);

where x indicates how many times to repeat the string or character.

#9 Inequality Operator

During filtering records in a SELECT statement, sometimes we have to use an inequality operator. Standard SQL defines <> as this operator, while T-SQL allows for both the standard operator and the != operator:

SELECT col3 FROM tab WHERE col1 != col2;
#10 ISNULL Function

In T-SQL, we have the ability to replace NULL values coming from a column using the ISNULL function. This is a function that is specific to T-SQL and is not in the SQL standard.

SELECT ISNULL(col1) FROM tab;
Which Parts of DML Syntax Are Different?

In T-SQL, the basic syntax of DELETE, UPDATE, and INSERT queries is the same as the SQL standard, but differences appear in more advanced queries. Let’s look at them.

#11 OUTPUT Keyword

The OUTPUT keyword occurs in DELETE, UPDATE, and INSERT statements. It is not defined in standard SQL.

Using T-SQL, we can see extra information returned by a query. It returns both old and new values in UPDATE or the values added using INSERT or deleted using DELETE. To see this information, we have to use prefixes in INSERT, UPDATE, and DELETE.

UPDATE tab SET col='new value'
OUTPUT Deleted.col, Inserted.col;

We see the result of changing records with the previous and new values in an updated column. The SQL standard does not support this feature.

#12 Syntax for INSERT INTO ... SELECT

Another structure of an INSERT query is INSERT INTO … SELECT. T-SQL allows you to insert data from another table into a destination table. Look at this query:

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

It is not a standard feature but a feature characteristic of SQL Server.

#13 FROM Clause in DELETE and UPDATE

SQL Server provides extended syntax of the UPDATE and DELETE with FROM clauses. You can use DELETE with FROM to use the rows from one table to remove corresponding rows in another table by referring to a primary key and a foreign key. Similarly, you can use UPDATE with FROM update rows from one table by referring to the rows of another table using common values (primary key in one table and foreign key in second, e.g. the same city name). Here is an example:

DELETE FROM Book
FROM Author
WHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

UPDATE Book
SET Book.Price=Book.Price*0.2
FROM Author
WHERE Book.AuthorId=Author.Id AND Author.Id=12;

The SQL standard doesn’t provide this syntax.

#14 INSERT, UPDATE, and DELETE With JOIN

You can also use INSERT, UPDATE, and DELETE using JOIN to connect to another table. An example of this is:

DELETE ItemOrder FROM ItemOrder
JOIN Item ON ItemOrder.ItemId=Item.Id
WHERE YEAR(Item.DeliveredDate) <= 2017;

This feature is not in the SQL standard.

Summary

This article does not cover all the issues about syntax differences between the SQL standard and T-SQL using the MS SQL Server system. However, this guide helps point out some basic features characteristic only of Transact-SQL and what SQL standard syntax isn’t implemented by MS SQL Server.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Originally published on https://dzone.com


How to Implement CRUD operations in Python on MySQL

How to Implement CRUD operations in Python on MySQL

In this post, we will see how to implement CRUD operations on MySQL

This post demonstrates how to implement CRUD operations in Python language on a MySQL database.

Python supports most of the popular databases to work with and implement CRUD operations. Some of the popular databases include:

  • MySQL
  • Oracle
  • PostgreSQL
  • Microsoft SQL Server 2000
  • Sybase
MySQL Database

If you want to work with CRUD operations in Python, first of all, download and install MySQL Database. If you already have a database, skip this step.

Go to below link and download MySQL Database: https://www.mysql.com/downloads/

Install MySQL Connector Driver

Python needs MySQL Connector Driver to access a MySQL Database. Follow the below steps to install the drive

STEP 1:

Open Command Prompt and navigate your pip.exe folder path.

By default pip folder path is: C:\Users\Acer\AppData\Local\Programs\Python\Python37-32\Scripts

STEP 2:

Type the below command and press Enter:

C:\Users\Acer\AppData\Local\Programs\Python\Python37-32\Scripts>pip install mysql-connector-python

After successful installation, exit from the command prompt**.**

Create Database

The following code snippet creates a new database. If you already have a database, you may skip this step. The database is created on the local server. You may want to use your server name, user id, and password.

To create a new database, we use CREATE Database SQL query.

The cursor() method creates a cursor object that is used to execute a SQL query by using the execute method. Once the database object is used, we need to close it.

import mysql.connector #Importing Connector package   
mysqldb=mysql.connector.connect(host="localhost",user="root",password="")#established connection   
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
mycursor.execute("create database dbpython")#Execute SQL Query to create a database    
mysqldb.close()#Connection Close  
Create a table

The following code snippet creates a new database table using CREATE TABLE SQL query.

#Create a table into dbpython database  
import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database   
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
mycursor.execute("create table student(roll INT,name VARCHAR(255), marks INT)")#Execute SQL Query to create a table into your database  
mysqldb.close()#Connection Close  
Insert Record

The INSERT INTO SQL query adds new records to the table.

mysqldb.commit() method commits the changes to the database.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
mycursor=mysqldb.cursor()#cursor() method create a cursor object    
try:  
   #Execute SQL Query to insert record  
   mycursor.execute("insert into student values(1,'Sarfaraj',80),(2,'Kumar',89),(3,'Sohan',90)")  
   mysqldb.commit() # Commit is used for your changes in the database  
   print('Record inserted successfully...')   
except:  
   # rollback used for if any error   
   mysqldb.rollback()  
mysqldb.close()#Connection Close  
Display Record

The following code uses a SELECT * SQL query to select data from a database table. The resultset is stored using cursor.fetchall() method.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
try:  
   mycursor.execute("select * from student")#Execute SQL Query to select all record   
   result=mycursor.fetchall() #fetches all the rows in a result set   
   for i in result:    
      roll=i[0]  
      name=i[1]  
      marks=i[2]  
      print(roll,name,marks)  
except:   
   print('Error:Unable to fetch data.')  
mysqldb.close()#Connection Close  

Note: If you want to fetch a single record then use fetchone() method

Update Record

The following code uses an UPDATE SQL query to update an existing record.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
try:  
   mycursor.execute("UPDATE student SET name='Ramu', marks=100 WHERE roll=1")#Execute SQL Query to update record
   mysqldb.commit() # Commit is used for your changes in the database  
   print('Record updated successfully...')   
except:   
   # rollback used for if any error  
   mysqldb.rollback()  
mysqldb.close()#Connection Close  
Delete Record

The following code uses a DELETE SQL query to delete a record from the table.

import mysql.connector   
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
mycursor=mysqldb.cursor()#cursor() method create a cursor object   
try:   
   mycursor.execute("DELETE FROM student WHERE roll=3")#Execute SQL Query to detete a record   
   mysqldb.commit() # Commit is used for your changes in the database  
   print('Record deteted successfully...')  
except:  
   # rollback used for if any error  
   mysqldb.rollback()  
mysqldb.close()#Connection Close  
Summary

In this post, I covered MySQL database operations (create database, create table, insert, display, update and delete) with Python and MySQL Connector driver. Thank you for reading!

SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

This SQL Full Course video will cover all the topics of SQL starting from scratch. This video is great for beginners who want to learn SQL and for advanced people to brush up their skills.


Thanks for watching

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Learn More

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

The Complete Oracle SQL Certification Course

MySQL Databases With Python Tutorial

SQL vs NoSQL or MySQL vs MongoDB

MySQL Database Tutorial for Beginners to Advanced Part 1/2

An Introduction to Queries in MySQL

How To Troubleshoot MySQL Queries?

SQL with MySQL - Complete Tutorial for Beginners

Build a Basic CRUD App with PHP and MySQL