Python MySQL Select From

To select data from mysql table, you must have some basic knowledge of SQL SELECT command.

The SELECT command is used to select data from Mysql tables.

The following syntax is used to select data with limited columns from a table :

SELECT column_name(s) FROM table_name

You can select ALL columns from the table with the * character:

SELECT * FROM table_name

You can use more options with SELECT statement to filter data using WHERE clause, getting ascending or descending ordered result using ORDER BY keyword, limiting the query result using LIMIT clause and more.

You can also search for these topics, define the mysql where using python, mysql where in python list, multiple conditions works in python mysql where, python mysql update where, where query and variable in python mysql, define the date and time method in python mysql where condition.

Select From a Table - fetchall() and fetchone() methods

We will take a sample table called "books" and it contains three columns ("id", "name", and "price") and three records.

ID	Name	Price
14	Python Networking	160.00
15	Web Programming	70.00
16	The Web with Python	120.00

The fetchall() method is used to return query results and each record as a python tuple.

Example :- Select all books from "books" table :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM books")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output :-

(14, 'Python Networking', 160.0)
(15, 'Web Programming', 70.0)
(16, 'The Web with Python', 120.0)

Note : We utilise a function fetchall(), which retrieves all rows from the last statement executed.

You can also search for these topics, python mysql select from multiple table, count the mysql select form using python, mysql database to select python form, example for python mysql select form, how to get mysql row count with python select from, highest value of select from using python mysql, how to handling the forms to be selected in python mysql, list the python select form in mysql database, from select no result in mysql python.

Selecting Columns

Use the "SELECT" statement followed by the Column name(s) to select just some of the columns in a table :

Example :- Select only columns of id and price :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT id, price FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output :-

(14, 160.0)
(15, 70.0)
(16, 120.0)

You can also search for these topics, python selecting columns by name, select the columns in python table, selecting columns by condition using python, number of columns to selecting in python, python type of columns to be selected, list the columns selected in python mysql, index the value of python selecting columns, python except the group by selecting python columns, how to get number of python selecting columns, Example for python selecting columns.

Using the fetchone() Method

You can use fetchone() method if you are only interested in one row.

The first row of result is returned with fetchone() method :

Example :- Fetch only one row from the query results

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM books")
myresult = mycursor.fetchone()
print(myresult)

Output :-

(14, 'Python Networking', 160.0)

You can also search for these topics, python using the fetchone method arguments, how to call the python using fetch the method, using the fetchone() method in python does not exist, check the python fetchone to method error, example for python method Using the fetchone(), working of function in python fetchone() method.

Python MySQL Where

To filter records, the WHERE clause with SELECT command is utilized.

The WHERE keyword only allows records that meet a specific requirement to be extracted.

The following statement is used to filter data from a table is :

SELECT column_name(s) FROM table_name WHERE column_name operator value

Related Links

converting decimal to binary in python

find mongodb python

variable in string python

create table in python mysql

python dict

factorial of a number using recursion in python

single level inheritance in python

python compile regex

accessing tuples python

format python string

Select With a Filter

You can filter the selection with the "WHERE" statement when you choose the record from a table :

Example :- Filter books by id = 15 and Filter books by price > 100 :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
# Filter books by id = 15
mycursor.execute("SELECT * FROM books WHERE id = 15")
myresult = mycursor.fetchall()
print("Filter books by id = 15\n")
for x in myresult:
  print(x)
# Filter books by price > 100
mycursor.execute("SELECT * FROM books WHERE price > 100")
myresult2 = mycursor.fetchall()
print("\nFilter books by price > 100\n")
for x in myresult2:
  print(x)

Output :-

Filter books by id = 15

(15, 'Web Programming', 70.0)

Filter books by price > 100

(14, 'Python Networking', 160.0)
(16, 'The Web with Python', 120.0)

You can also search for these topics,python select with a filter is empty, select the mysql where in python by key, define the select filter condition using python mysql, python select with filter column by value, how to get value to select filter in python, get one python select with filter, python get the result of filters to select, define the highest number in python select with a filter, Example for python filter select filter.

Wildcard Characters

The records that start, include or end with a certain letter or phrase can also be selected.

Use the % to represent the characters of the wildcards :

Example :- Select records where the word "web" is found in the name column :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM books WHERE name Like '%web%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output :-

(15, 'Web Programming', 70.0)
(16, 'The Web with Python', 120.0)

You can also search for these topics, python wildcard characters batch, count the python wildcard characters, example for characters in python wildcard, expansion of wildcard characters in python, python search in list and key with wildcard characters, Example for wildcard characters in python.

Prevent SQL Injection

If the user provides query values, the values should be escape.

This prevents SQL injections, a frequent approach for web hacking, to remove or misuse your database.

Methods to avoid query values in the mysql.connector module :

Example :- Escape query values utilising the % technique of placholder :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
qry = "SELECT * FROM books WHERE name Like %s and price > %s"
val = ("%python%", 150)
mycursor.execute(qry, val)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output :-

(14, 'Python Networking', 160.0)

You can also search for these topics, python sql injection check, detection of sql injection to prevent in python, how to escape the sql injection using python, prevent basic injection to python prevent, example for python sql injection to prevent, filter the prevent python sql injection, how python works and prevent from sql injection.

Python MySQL Order By

To arrange the result in ascending or descending order, use the ORDER BY declaration.

By default, the term ORDER BY orders the result up. Use the DESC keyword to sort the output in decreasing order.

Related Links

boolean values and operators in python

python parse json

object oriented programming python course

create mysql database using python

python substring

python str to datetime

python sql

learning python 3 for free

binary operator in python

python mongodb update

You can also search for these topics, python mysql order by and group, convert the mysql order by python, python connection of mysql order by method, example for python mysql order by method, execute the python mysql order by error, get data of the mysql database in python order by, how to install order by using python mysql, how to update the key order by in python mysql.

Sort the Result

The following syntax is used to sort a query result :

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

Example :- Display results in ascending order based on price column :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM books ORDER BY price"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output :-

(15, 'Web Programming', 70.0)
(16, 'The Web with Python', 120.0)
(14, 'Python Networking', 160.0)

You can also search for these topics, python sort the result of a function, list the python mysql sort of result, define the value of python to sort the result, working of python function to sorting the result, python difference between the result sorting, how to empty the python sorting, value of key to python result sorting, Example for python sort the result.

ORDER BY DESC

To sort the result in a descending order, use the DESC keyword.

Example :- Numerically sort the result reverse by id column :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY id DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output :-

(16, 'The Web with Python', 120.0)
(15, 'Web Programming', 70.0)
(14, 'Python Networking', 160.0)

You can also search for these topics, python order by desc get, how to use the python order by desc, python order by desc multiple columns, python order by desc range, use the order by desc using python, Example for python order by desc.

Python MySQL Limit

The LIMIT clause in MySQL is used to limit how many records are returned from query results.

LIMIT Data highly useful on big tables to easily implement pagination results with SQL. A huge quantity of data can have an effect on performance.

Suppose we would like to pick from the "books" table all records from 1 - 20 (included)." The SQL query looks like that :

$sql = "SELECT * FROM books LIMIT 20";

You can also search for these topics, python mysql limit alternative, mysql limit between using python, mysql to create table limit in python, how to delete the python mysql limit, example for python mysql limit, handling the errors in python mysql limit, working of limit of the python mysql function, python mysql limit to keep connection open, key update the python limit with mysql database.

Limit the Result

Using the statement "LIMIT", you can reduce the number of records you return from the query :

Example :- In the table "books" choose the first 2 records :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM books LIMIT 2")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output :-

(14, 'Python Networking', 160.0)
(15, 'Web Programming', 70.0)

You can also search for these topics, python limit the result of a function, decimal places to limit the result in python, set the python limit the result, example for python limit the result.

Start From Another Position

You can use the "OFFSET" keyword if you wish to return 5 files beginning with the third record :

Example :- Return 1 record from start position 2 :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output :-

(16, 'The Web with Python', 120.0)

You can also search for these topics, python start from another position a function, position a file in python start from another position, position in backround to start another using python, Example for python Start From Another Position, define the records of python start form another position.

Python MySQL Join Two or More Tables

It is possible to work with multiple tables in python with mysql.

You can use JOIN statements to merge rows from two or more tables based on a related column.

You can also search for these topics, python mysql join two tables, delete the mysql join in python, default value of python mysql join, mysql documentation using python join, example for python mysql join, how to handling the errors with python mysql join, define the python first match with mysql join, insert the python join with mysql database, key value of join using python mysql, python multiple tables with condition in mysql join.

All Tutorials