Select all mysql python

Чтение и получение данных из таблиц MySQL в Python

Оператор SELECT используется для чтения значений и получения данных из таблиц баз MySQL в Python. Мы можем ограничить вывод запроса на выборку, используя различные предложения в SQL, такие как where, limit и т. д.

Python предоставляет метод fetchall(), который возвращает данные, хранящиеся внутри таблицы, в виде строк. Мы можем повторить результат, чтобы получить отдельные строки.

В этом разделе руководства мы извлечем данные из базы данных с помощью скрипта python. Мы также отформатируем вывод, чтобы распечатать его на консоли.

import mysql.connector #Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") #creating the cursor object cur = myconn.cursor() try: #Reading the Employee data cur.execute("select * from Employee") #fetching the rows from the cursor object result = cur.fetchall() #printing the result for x in result: print(x); except: myconn.rollback() myconn.close()

('John', 101, 25000.0, 201, 'Newyork') ('John', 102, 25000.0, 201, 'Newyork') ('David', 103, 25000.0, 202, 'Port of spain') ('Nick', 104, 90000.0, 201, 'Newyork') ('Mike', 105, 28000.0, 202, 'Guyana')

Чтение определенных столбцов

Мы можем прочитать конкретные столбцы, указав их имена вместо звездочки(*).

В следующем примере мы прочитаем name, id и salary из таблицы Employee и напечатаем их на консоли.

import mysql.connector #Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") #creating the cursor object cur = myconn.cursor() try: #Reading the Employee data cur.execute("select name, id, salary from Employee") #fetching the rows from the cursor object result = cur.fetchall() #printing the result for x in result: print(x); except: myconn.rollback() myconn.close()

('John', 101, 25000.0) ('John', 102, 25000.0) ('David', 103, 25000.0) ('Nick', 104, 90000.0) ('Mike', 105, 28000.0)

Метод fetchone()

Метод fetchone() используется для выборки только одной строки из таблицы. Метод fetchone() возвращает следующую строку набора результатов.

Рассмотрим следующий пример.

import mysql.connector #Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") #creating the cursor object cur = myconn.cursor() try: #Reading the Employee data cur.execute("select name, id, salary from Employee") #fetching the first row from the cursor object result = cur.fetchone() #printing the result print(result) except: myconn.rollback() myconn.close()

Форматирование результата

Мы можем отформатировать результат, перебирая созданный методом fetchall() или fetchone() объект курсора, поскольку результат существует как объект кортежа, который не читается.

import mysql.connector #Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") #creating the cursor object cur = myconn.cursor() try: #Reading the Employee data cur.execute("select name, id, salary from Employee") #fetching the rows from the cursor object result = cur.fetchall() print("Name id Salary"); for row in result: print("%s %d %d"%(row[0],row[1],row[2])) except: myconn.rollback() myconn.close()

Name id Salary John 101 25000 John 102 25000 David 103 25000 Nick 104 90000 Mike 105 28000

Использование предложения where

Мы можем ограничить результат, полученный оператором select, используя предложение where. Будет извлечены только те столбцы, которые удовлетворяют условию where.

Рассмотрим следующий пример.

Пример: печать имен, начинающихся с j

import mysql.connector #Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") #creating the cursor object cur = myconn.cursor() try: #Reading the Employee data cur.execute("select name, id, salary from Employee where name like 'J%'") #fetching the rows from the cursor object result = cur.fetchall() print("Name id Salary"); for row in result: print("%s %d %d"%(row[0],row[1],row[2])) except: myconn.rollback() myconn.close()

Name id Salary John 101 25000 John 102 25000

Пример: печать имен с 102 и 103

import mysql.connector #Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") #creating the cursor object cur = myconn.cursor() try: #Reading the Employee data cur.execute("select name, id, salary from Employee where id in(101,102,103)") #fetching the rows from the cursor object result = cur.fetchall() print("Name id Salary"); for row in result: print("%s %d %d"%(row[0],row[1],row[2])) except: myconn.rollback() myconn.close()

Name id Salary John 101 25000 John 102 25000 David 103 2500

Упорядочение результата

Предложение ORDER BY используется для упорядочивания результата. Рассмотрим следующий пример.

import mysql.connector #Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") #creating the cursor object cur = myconn.cursor() try: #Reading the Employee data cur.execute("select name, id, salary from Employee order by name") #fetching the rows from the cursor object result = cur.fetchall() print("Name id Salary"); for row in result: print("%s %d %d"%(row[0],row[1],row[2])) except: myconn.rollback() myconn.close()

Name id Salary David 103 25000 John 101 25000 John 102 25000 Mike 105 28000 Nick 104 90000

Order by DESC

Это предложение упорядочивает результат в порядке убывания определенного столбца.

import mysql.connector #Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") #creating the cursor object cur = myconn.cursor() try: #Reading the Employee data cur.execute("select name, id, salary from Employee order by name desc") #fetching the rows from the cursor object result = cur.fetchall() #printing the result print("Name id Salary"); for row in result: print("%s %d %d"%(row[0],row[1],row[2])) except: myconn.rollback() myconn.close()

Name id Salary Nick 104 90000 Mike 105 28000 John 101 25000 John 102 25000 David 103 25000

Источник

How to select all the data from a table using MySQL in Python?

The tables in MySQL consist of rows and columns. The columns specify the fields and the rows specify the records of data. The data in the tables needs to be fetched to use. We may at times need to fetch all the data from the MySQL table.

All the rows can be fetched from the table using the SELECT * statement.

Syntax

The * in the above syntax means to fetch all the rows from the table.

Steps you need to follow to select all the data from a table using MySQL in python

• import MySQL connector
• establish connection with the connector using connect()
• create the cursor object using cursor() method
• create a query using the appropriate mysql statements
• execute the SQL query using execute() method
• close the connection

Suppose we have a table named ‘MyTable’ and we want to fetch all the data from this table.

+----------+---------+-----------+------------+ | Name | Class | City | Marks | +----------+---------+-----------+------------+ | Karan | 4 | Amritsar | 95 | | Sahil | 6 | Amritsar | 93 | | Kriti | 3 | Batala | 88 | | Khushi | 9 | Delhi | 90 | | Kirat | 5 | Delhi | 85 | +----------+---------+-----------+------------+

Example

import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="SELECT * FROM MyTable" cursor.execute(query) for row in cursor: print(row)

The above code execute the select * query which fetches all the rows from the table. Later , we print all the rows using for statement.

Output

(‘Karan’, 4, ‘Amritsar’ , 95) (‘Sahil’ , 6, ‘Amritsar’ ,93) (‘Kriti’ , 3 ‘Batala’ , 88) (‘Khushi’ , 9, ‘Delhi’ , 90) (‘Kirat’ , 5, ‘Delhi’ ,85)

Источник

Python MySQL Select

In this Python MySQL Select tutorial, we are going to learn all about how to select all the records from the table using the Python MySQL connector.
In the previous tutorial, we have seen a complete process to insert new records into the database.

Make sure you have already installed MySQL connector in your machine using pip as well as create database and table.

Python MySQL Select

To select all the records from the database table, MySQL provide “SELECT” statement.

Example:

Here we will select all the records from the table named “students“.

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="root21", database = "demodb", port = 3308 ) cursor = mydb.cursor() cursor.execute('SELECT * FROM students') all_students = cursor.fetchall() for record in all_students: print(record)

(1, 'Vishvajit', 12) (2, 'John', 22) (3, 'Alex', 32) (4, 'Peter', 12) (5, 'Alex Peter', 12) (6, 'Alsena', 45) (7, 'Jaini', 56)

SELECT Columns:

Here we will select some specific columns from the table named “students“.

Example:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="root21", database = "demodb", port = 3308 ) cursor = mydb.cursor() cursor.execute('SELECT id, name FROM students') all_students = cursor.fetchall() for record in all_students: print(record)

(1, 'Vishvajit') (2, 'John') (3, 'Alex') (4, 'Peter') (5, 'Alex Peter') (6, 'Alsena') (7, 'Jaini')

Note:- Above credentials may be different according to your MySQL configuration. I am showing all the credentials for only testing, It will not working in your case.

Select Only first row

To select only first row from the table, use fetchone() method.

Example:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="root21", database = "demodb", port = 3308 ) cursor = mydb.cursor() cursor.execute('SELECT id, name FROM students') student = cursor.fetchone() print(student)

Output will be:- (1, ‘Vishvajit’)

Use LIMIT statement:

When you want to select first five records, Then you can use LIMIT keyword.

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="root21", database = "demodb", port = 3308 ) cursor = mydb.cursor() cursor.execute('SELECT * FROM students LIMIT 5') all_students = cursor.fetchall() for record in all_students: print(record)

(1, 'Vishvajit', 12) (2, 'John', 22) (3, 'Alex', 32) (4, 'Peter', 12) (5, 'Alex Peter', 12)

Conclusion

In this Python MySQL Select, you have learned all about how to select records from the database table.
Python MySQL connector is the best for connecting Python applications to the MySQL database and executes SQL queries. After created of database successfully, you can perform a query on that particular database.

If this article for you, please share and keep visiting for further Python MySQL database tutorials.

More Tutorials

More about Python MySQL select:- Click Here

Источник

Python MySQL Select From

To select from a table in MySQL, use the «SELECT» statement:

Example

Select all records from the «customers» table, and display the result:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

mycursor.execute(«SELECT * FROM customers»)

Note: We use the fetchall() method, which fetches all rows from the last executed statement.

Selecting Columns

To select only some of the columns in a table, use the «SELECT» statement followed by the column name(s):

Example

Select only the name and address columns:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

mycursor.execute(«SELECT name, address FROM customers»)

Using the fetchone() Method

If you are only interested in one row, you can use the fetchone() method.

The fetchone() method will return the first row of the result:

Example

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

mycursor.execute(«SELECT * FROM customers»)

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

Thank You For Helping Us!

Your message has been sent to W3Schools.

Top Tutorials

Top References

Top Examples

Get Certified

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.

Источник