Category: python Data Access

Python MySQL – Select Data ”; Previous Next You can retrieve/fetch data from a table in MySQL using the SELECT query. This query/statement returns contents of the specified table in tabular form and it is called as result-set. Syntax Following is the syntax of the SELECT query − SELECT column1, column2, columnN FROM table_name; Example Assume we have created a table in MySQL with name cricketers_data as − CREATE TABLE cricketers_data( First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); And if we have inserted 5 records in to it using INSERT statements as − insert into cricketers_data values( ”Shikhar”, ”Dhawan”, DATE(”1981-12-05”), ”Delhi”, ”India”); insert into cricketers_data values( ”Jonathan”, ”Trott”, DATE(”1981-04-22”), ”CapeTown”, ”SouthAfrica”); insert into cricketers_data values( ”Kumara”, ”Sangakkara”, DATE(”1977-10-27”), ”Matale”, ”Srilanka”); insert into cricketers_data values( ”Virat”, ”Kohli”, DATE(”1988-11-05”), ”Delhi”, ”India”); insert into cricketers_data values( ”Rohit”, ”Sharma”, DATE(”1987-04-30”), ”Nagpur”, ”India”); Following query retrieves the FIRST_NAME and Country values from the table. mysql> select FIRST_NAME, Country from cricketers_data; +————+————-+ | FIRST_NAME | Country | +————+————-+ | Shikhar | India | | Jonathan | SouthAfrica | | Kumara | Srilanka | | Virat | India | | Rohit | India | +————+————-+ 5 rows in set (0.00 sec) You can also retrieve all the values of each record using * instated of the name of the columns as − mysql> SELECT * from cricketers_data; +————+————+—————+—————-+————-+ | First_Name | Last_Name | Date_Of_Birth | Place_Of_Birth | Country | +————+————+—————+—————-+————-+ | Shikhar | Dhawan | 1981-12-05 | Delhi | India | | Jonathan | Trott | 1981-04-22 | CapeTown | SouthAfrica | | Kumara | Sangakkara | 1977-10-27 | Matale | Srilanka | | Virat | Kohli | 1988-11-05 | Delhi | India | | Rohit | Sharma | 1987-04-30 | Nagpur | India | +————+————+—————+—————-+————-+ 5 rows in set (0.00 sec) Reading data from a MYSQL table using Python READ Operation on any database means to fetch some useful information from the database. You can fetch data from MYSQL using the fetch() method provided by the mysql-connector-python. The cursor.MySQLCursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where, The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones). The fetchone() method fetches the next row in the result of a query and returns it as a tuple. The fetchmany() method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row. Note − A result set is an object that is returned when a cursor object is used to query a table. rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method. Example Following example fetches all the rows of the EMPLOYEE table using the SELECT query and from the obtained result set initially, we are retrieving the first row using the fetchone() method and then fetching the remaining rows using the fetchall() method. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row sql = ”””SELECT * from EMPLOYEE””” #Executing the query cursor.execute(sql) #Fetching 1st row from the table result = cursor.fetchone(); print(result) #Fetching 1st row from the table result = cursor.fetchall(); print(result) #Closing the connection conn.close() Output (”Krishna”, ”Sharma”, 19, ”M”, 2000.0) [(”Raj”, ”Kandukuri”, 20, ”M”, 7000.0), (”Ramya”, ”Ramapriya”, 25, ”M”, 5000.0)] Following example retrieves first two rows of the EMPLOYEE table using the fetchmany() method. Example import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row sql = ”””SELECT * from EMPLOYEE””” #Executing the query cursor.execute(sql) #Fetching 1st row from the table result = cursor.fetchmany(size =2); print(result) #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 19, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 20, ”M”, 7000.0)] Print Page Previous Next Advertisements ”;

Python MySQL – Create Table ”; Previous Next The CREATE TABLE statement is used to create tables in MYSQL database. Here, you need to specify the name of the table and, definition (name and datatype) of each column. Syntax Following is the syntax to create a table in MySQL − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, ); Example Following query creates a table named EMPLOYEE in MySQL with five columns namely, FIRST_NAME, LAST_NAME, AGE, SEX and, INCOME. mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); Query OK, 0 rows affected (0.42 sec) The DESC statement gives you the description of the specified table. Using this you can verify if the table has been created or not as shown below − mysql> Desc Employee; +————+———-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————+———-+——+—–+———+——-+ | FIRST_NAME | char(20) | NO | | NULL | | | LAST_NAME | char(20) | YES | | NULL | | | AGE | int(11) | YES | | NULL | | | SEX | char(1) | YES | | NULL | | | INCOME | float | YES | | NULL | | +————+———-+——+—–+———+——-+ 5 rows in set (0.07 sec) Creating a table in MySQL using python The method named execute() (invoked on the cursor object) accepts two variables − A String value representing the query to be executed. An optional args parameter which can be a tuple or, list or, dictionary, representing the parameters of the query (values of the place holders). It returns an integer value representing the number of rows effected by the query. Once a database connection is established, you can create tables by passing the CREATE TABLE query to the execute() method. In short, to create a table using python 7minus; Import mysql.connector package. Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Create a cursor object by invoking the cursor() method on the connection object created above. Then, execute the CREATE TABLE statement by passing it as a parameter to the execute() method. Example Following example creates a table named Employee in the database mydb. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Dropping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) #Creating table as per requirement sql =”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )””” cursor.execute(sql) #Closing the connection conn.close() Print Page Previous Next Advertisements ”;

Python MySQL – Order By ”; Previous Next While fetching data using SELECT query, you can sort the results in desired order (ascending or descending) using the OrderBy clause. By default, this clause sorts results in ascending order, if you need to arrange them in descending order you need to use “DESC” explicitly. Syntax Following is the syntax SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2,.. columnN] [ASC | DESC]; of the ORDER BY clause: Example Assume we have created a table in MySQL with name EMPLOYEES as − mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); Query OK, 0 rows affected (0.36 sec) And if we have inserted 4 records in to it using INSERT statements as − mysql> INSERT INTO EMPLOYEE VALUES (”Krishna”, ”Sharma”, 19, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 25, ”F”, 5000), (”Mac”, ”Mohan”, 26, ”M”, 2000); Following statement retrieves the contents of the EMPLOYEE table in ascending order of the age. mysql> SELECT * FROM EMPLOYEE ORDER BY AGE; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Krishna | Sharma | 19 | M | 2000 | | Raj | Kandukuri | 20 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | | Mac | Mohan | 26 | M | 2000 | +————+———–+——+——+——–+ 4 rows in set (0.04 sec) You can also retrieve data in descending order using DESC as − mysql> SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME, INCOME DESC; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Krishna | Sharma | 19 | M | 2000 | | Mac | Mohan | 26 | M | 2000 | | Raj | Kandukuri | 20 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | +————+———–+——+——+——–+ 4 rows in set (0.00 sec) ORDER BY clause using python To retrieve contents of a table in specific order, invoke the execute() method on the cursor object and, pass the SELECT statement along with ORDER BY clause, as a parameter to it. Example In the following example we are creating a table with name and Employee, populating it, and retrieving its records back in the (ascending) order of their age, using the ORDER BY clause. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) sql = ”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )””” cursor.execute(sql) #Populating the table insert_stmt = “INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s)” data = [(”Krishna”, ”Sharma”, 26, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 29, ”F”, 5000), (”Mac”, ”Mohan”, 26, ”M”, 2000)] cursor.executemany(insert_stmt, data) conn.commit() #Retrieving specific records using the ORDER BY clause cursor.execute(“SELECT * from EMPLOYEE ORDER BY AGE”) print(cursor.fetchall()) #Closing the connection conn.close() Output [(”Raj”, ”Kandukuri”, 20, ”M”, 7000.0), (”Krishna”, ”Sharma”, 26, ”M”, 2000.0), (”Mac”, ”Mohan”, 26, ”M”, 2000.0), (”Ramya”, ”Ramapriya”, 29, ”F”, 5000.0) ] In the same way you can retrieve data from a table in descending order using the ORDER BY clause. Example import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving specific records using the ORDERBY clause cursor.execute(“SELECT * from EMPLOYEE ORDER BY INCOME DESC”) print(cursor.fetchall()) #Closing the connection conn.close() Output [(”Raj”, ”Kandukuri”, 20, ”M”, 7000.0), (”Ramya”, ”Ramapriya”, 29, ”F”, 5000.0), (”Krishna”, ”Sharma”, 26, ”M”, 2000.0), (”Mac”, ”Mohan”, 26, ”M”, 2000.0) ] Print Page Previous Next Advertisements ”;

Python PostgreSQL – Create Database ”; Previous Next You can create a database in PostgreSQL using the CREATE DATABASE statement. You can execute this statement in PostgreSQL shell prompt by specifying the name of the database to be created after the command. Syntax Following is the syntax of the CREATE DATABASE statement. CREATE DATABASE dbname; Example Following statement creates a database named testdb in PostgreSQL. postgres=# CREATE DATABASE testdb; CREATE DATABASE You can list out the database in PostgreSQL using the l command. If you verify the list of databases, you can find the newly created database as follows − postgres=# l List of databases Name | Owner | Encoding | Collate | Ctype | ———–+———-+———-+—————————-+————-+ mydb | postgres | UTF8 | English_United States.1252 | ……….. | postgres | postgres | UTF8 | English_United States.1252 | ……….. | template0 | postgres | UTF8 | English_United States.1252 | ……….. | template1 | postgres | UTF8 | English_United States.1252 | ……….. | testdb | postgres | UTF8 | English_United States.1252 | ……….. | (5 rows) You can also create a database in PostgreSQL from command prompt using the command createdb, a wrapper around the SQL statement CREATE DATABASE. C:Program FilesPostgreSQL11bin> createdb -h localhost -p 5432 -U postgres sampledb Password: Creating a database using python The cursor class of psycopg2 provides various methods execute various PostgreSQL commands, fetch records and copy data. You can create a cursor object using the cursor() method of the Connection class. The execute() method of this class accepts a PostgreSQL query as a parameter and executes it. Therefore, to create a database in PostgreSQL, execute the CREATE DATABASE query using this method. Example Following python example creates a database named mydb in PostgreSQL database. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”postgres”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Preparing query to create a database sql = ”””CREATE database mydb”””; #Creating a database cursor.execute(sql) print(“Database created successfully……..”) #Closing the connection conn.close() Output Database created successfully…….. Print Page Previous Next Advertisements ”;

Python MySQL – Update Table ”; Previous Next UPDATE Operation on any database updates one or more records, which are already available in the database. You can update the values of existing records in MySQL using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it. Syntax Following is the syntax of the UPDATE statement in MySQL − UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition]; You can combine N number of conditions using the AND or the OR operators. Example Assume we have created a table in MySQL with name EMPLOYEES as − mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); Query OK, 0 rows affected (0.36 sec) And if we have inserted 4 records in to it using INSERT statements as − mysql> INSERT INTO EMPLOYEE VALUES (”Krishna”, ”Sharma”, 19, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 25, ”F”, 5000), (”Mac”, ”Mohan”, 26, ”M”, 2000); Following MySQL statement increases the age of all male employees by one year − mysql> UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ”M”; Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0 If you retrieve the contents of the table, you can see the updated values as − mysql> select * from EMPLOYEE; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Krishna | Sharma | 20 | M | 2000 | | Raj | Kandukuri | 21 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | | Mac | Mohan | 27 | M | 2000 | +————+———–+——+——+——–+ 4 rows in set (0.00 sec) Updating the contents of a table using Python To update the records in a table in MySQL using python − import mysql.connector package. Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Create a cursor object by invoking the cursor() method on the connection object created above. Then, execute the UPDATE statement by passing it as a parameter to the execute() method. Example The following example increases age of all the males by one year. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Preparing the query to update the records sql = ”””UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ”M” ””” try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database conn.commit() except: # Rollback in case there is any error conn.rollback() #Retrieving data sql = ”””SELECT * from EMPLOYEE””” #Executing the query cursor.execute(sql) #Displaying the result print(cursor.fetchall()) #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 22, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 23, ”M”, 7000.0), (”Ramya”, ”Ramapriya”, 26, ”F”, 5000.0) ] Print Page Previous Next Advertisements ”;

Python MySQL – Drop Table ”; Previous Next You can remove an entire table using the DROP TABLE statement. You just need to specify the name of the table you need to delete. Syntax Following is the syntax of the DROP TABLE statement in MySQL − DROP TABLE table_name; Example Before deleting a table get the list of tables using the SHOW TABLES statement as follows − mysql> SHOW TABLES; +—————–+ | Tables_in_mydb | +—————–+ | contact | | cricketers_data | | employee | | sample | | tutorials | +—————–+ 5 rows in set (0.00 sec) Following statement removes the table named sample from the database completely − mysql> DROP TABLE sample; Query OK, 0 rows affected (0.29 sec) Since we have deleted the table named sample from MySQL, if you get the list of tables again you will not find the table name sample in it. mysql> SHOW TABLES; +—————–+ | Tables_in_mydb | +—————–+ | contact | | cricketers_data | | employee | | tutorials | +—————–+ 4 rows in set (0.00 sec) Removing a table using python You can drop a table whenever you need to, using the DROP statement of MYSQL, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table. To drop a table from a MYSQL database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it. Example Following table drops a table named EMPLOYEE from the database. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving the list of tables print(“List of tables in the database: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Doping EMPLOYEE table if already exists cursor.execute (“DROP TABLE EMPLOYEE”) print(“Table dropped… “) #Retrieving the list of tables print( “List of tables after dropping the EMPLOYEE table: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Closing the connection conn.close() Output List of tables in the database: [(”employee”,), (”employeedata”,), (”sample”,), (”tutorials”,)] Table dropped… List of tables after dropping the EMPLOYEE table: [(”employeedata”,), (”sample”,), (”tutorials”,)] Drop table only if exists If you try to drop a table which does not exist in the database, an error occurs as − mysql.connector.errors.ProgrammingError: 1051 (42S02): Unknown table ”mydb.employee” You can prevent this error by verifying whether the table exists before deleting, by adding the IF EXISTS to the DELETE statement. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving the list of tables print(“List of tables in the database: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Doping EMPLOYEE table if already exists cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) print(“Table dropped… “) #Retrieving the list of tables print(“List of tables after dropping the EMPLOYEE table: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Closing the connection conn.close() Output List of tables in the database: [(”employeedata”,), (”sample”,), (”tutorials”,)] Table dropped… List of tables after dropping the EMPLOYEE table: [(”employeedata”,), (”sample”,), (”tutorials”,)] Print Page Previous Next Advertisements ”;