Category

Python

Category

Use the ORDER BY statement to sort the result in ascending order. import mysql.connector as mysql db = mysql.connect( host =”localhost”, username= “root”, password = “root@123”, database = “realprogrammers” ) cursor = db.cursor() # defining the query query = “SELECT * FROM user ORDER BY user_name” cursor.execute(query) result = cursor.fetchall() for x in result: print(x) Fetch data only column import mysql.connector as mysql db = mysql.connect( host =”localhost”, username= “root”, password = “root@123”, database = “realprogrammers” ) cursor = db.cursor() # defining the query query =…

Update keyword is used to Update the records from the table. import mysql.connector as mysql db = mysql.connect( host =”localhost”, username= “root”, password = “root@123”, database = “realprogrammers” ) cursor = db.cursor() # defining the query query = “UPDATE user SET user_name = ‘Saurabhshukla018’ WHERE name =’Saurabh'” cursor.execute(query) db.commit() print(cursor.rowcount, “record(s) affected”)

DELETE keyword is used to delete the records from the table. DELETE FROM table_name WHERE condition statement is used to delete records. If you don’t specify the condition, then all of the records will be deleted. Let’s delete a record from the user table with named Siddharth. import mysql.connector as mysql db = mysql.connect( host=”localhost”, user=”root”, passwd=”root@123″, database=”realprogrammer” ) cursor = db.cursor() ## defining the Query query = “DELETE FROM users WHERE name = ‘Siddharth'”…

Where is used to select data on condition. Now, we will select a record with name Siddharth. SELECT column_name FROM table_name WHERE condition statement will be used to retrieve the data on some condition. import mysql.connector as mysql db = mysql.connect( host=”localhost”, user=”root”, passwd=”root@123″, database=”realprogrammer” ) cursor = db.cursor() ## defining the Query query = “SELECT * FROM user WHERE name = ‘Siddharth'” ## getting records from the table cursor.execute(query) ## fetching all records from…

import mysql.connector as mysql db = mysql.connect( host=”localhost”, user=”root”, passwd=”root@123″, database=”realprogrammer” ) cursor = db.cursor() ## defining the Query query = “SELECT user_name FROM user” ## getting ‘user_name’ column from the table cursor.execute(query) ## fetching all usernames from the ‘cursor’ object usernames = cursor.fetchall() for username in usernames: print(username)

To retrieve the data from a table we use, SELECT column_names FROM table_name statement. Getting All Records From Table import mysql.connector as mysql db = mysql.connect( host=”localhost”, user=”root”, passwd=”root@123″, database=”realprogrammer” ) cursor = db.cursor() ## defining the Query query = “SELECT * FROM user” ## getting records from the table cursor.execute(query) records = cursor.fetchall() ## Showing data for record in records: print(record)

Inserting Multiple Rows You can check how to insert multiple rows into the table. To insert multiple rows into the table, we use the executemany() method. It takes a list of tuples containing the data as a second parameter and a query as the first argument. import mysql.connector as mysql db = mysql.connect( host=”localhost”, user=”root”, passwd=”root@123″, database=”realprogrammer” ) cursor = db.cursor() ## defining the Query query = “INSERT INTO user (name, user_name) VALUES (%s, %s)”…

Inserting data in Python Inserting data into a table to store it. Use INSERT INTO table_name (column_names) VALUES (data) statement to insert into the table. Inserting A Single Row you can read how to insert a single row into the table. import mysql.connector as mysql db = mysql.connect( host=”localhost”, user=”root”, passwd=”root@123″, database=”realprogrammer” ) cursor = db.cursor() query = “INSERT INTO user (name, user_name) VALUES (%s, %s)” ## storing values in a variable values = (“Real…

Creating tables in the database to store data. Before creating tables, we have to select a database first. Run the following code, to select realprogrammer database which we have selected. ## Connecting to the database import mysql.connector as mysql db = mysql.connect( host=”localhost”, user=”root”, passwd=”root@123″, db=”realprogrammer” ) cursor = db.cursor() ## creating a table called ‘users’ in the ‘datacamp’ database cursor.execute( “CREATE TABLE user (name VARCHAR(255), user_name VARCHAR(255))”)

Create a folder and go inside of the folder mkdir python_database cd python_database Installation pip install mysql-connector-python Now Create file db.py ## Connecting to the database import mysql.connector as mysql db = mysql.connect( host=”localhost”, user=”root”, passwd=”root@123″ ) print(db)