Вывод данных mysql python

2. MySQL with Python¶

The ‘mysql-connector’ is not supported by Django-framework. The good option is ‘mysqlclient’ which is supported by Django as well.

$ mysql -u root -p Enter password: mysql> CREATE DATABASE pythonSQL; 

2.2. Connect and load data¶

Following code can be used to connect and load the data to database. Note that, the commands in the c.execute(…) statements are exactly same as the commands in the previous chapters.

# create_fill_database.py import mysql.connector as mc # connect to database conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL') c = conn.cursor() # cursor to perform operations def create_table(): """ Create table in the database """ # optional: drop table if exists c.execute('DROP TABLE IF EXISTS writer') c.execute('CREATE TABLE writer \ ( \ id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, \ name VARCHAR(30) NOT NULL UNIQUE, \ age int \ )' ) def insert_data(): """ Insert data to the table """ c.execute("INSERT INTO writer (name) VALUES ('Pearl Buck')") c.execute(" INSERT INTO writer VALUES \ (NULL, 'Rabindranath Tagore', 80), \ (NULL, 'Leo Tolstoy', 82)" \ ) c.execute(" INSERT INTO writer (age, name) VALUES \ (30, 'Meher Krishna Patel')" \ ) def commit_close(): """ commit changes to database and close connection """ conn.commit() c.close() conn.close() def main(): """ execute create and insert commands """ create_table() insert_data() commit_close() # required for save the changes # standard boilerplate to call main function if __name__ == '__main__': main() 

$ python create_fill_database.py

2.3. Read data from table¶

Following code can be used to read data from the table,

# read_database.py import mysql.connector as mc conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL') c = conn.cursor() def read_data(): c.execute('SELECT * FROM writer') writers = c.fetchall() # data is read in the form of list for writer in writers: # print individual item in the list print(writer) # data at each row is saved as tuple def main(): read_data() if __name__ == '__main__': main() 

$ python read_database.py (1, 'Pearl Buck', None) (2, 'Rabindranath Tagore', 80) (3, 'Leo Tolstoy', 82) (4, 'Meher Krishna Patel', 30) 

• In this way, we can get the data from the table and perform various operations on the data.
• Also, we can use all those queries with python, as queries in the execute statements are same as queries in previous chapter.

2.4. Connection in try-except block¶

We can use following code to put the connection string in the try except block, so that we can get proper message for not connecting with the database,

# connect_try.py import mysql.connector as mq from mysql.connector import errorcode try: conn = mq.connect(host='localhost', user='root', password='d', db='pythonSQL') print("Connected") except mq.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(err) else: print("Connection closed") conn.close() 

$ python connect_try.py Connected Connection closed

$ python connect_try.py Something is wrong with your user name or password

$ python connect_try.py Database does not exist

© Copyright 2017, Meher Krishna Patel. Revision 31d452b4 .

Versions latest Downloads pdf html epub On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.

Источник

Чтение и получение данных из таблиц 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

Источник