Apa itu perintah kursor di python?

Question

Artikel ini menunjukkan penggunaan metode kelas kursor Python fetchall(),

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

0, dan

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

1 untuk mengambil baris dari tabel database. Artikel ini berlaku untuk semua database relasional, misalnya, SQLite, MySQL, PostgreSQL

Table of Contents Show

Ambil semua baris dari tabel database menggunakan kursor's fetchall()
Ambil beberapa baris dari tabel menggunakan Connected to database Total rows are: 5 Printing each row Id: 1 Name: Emma Email: [email protected] Salary: 12000.0 Id: 2 Name: Scott Email: [email protected] Salary: 22000.0 Id: 3 Name: Jessa Email: [email protected] Salary: 18000.0 Id: 4 Name: Mike Email: [email protected] Salary: 13000.0 Id: 5 Name: Ricky Email: [email protected] Salary: 19000.0 The Sqlite connection is closed6
Apa yang akan terjadi jika kursor mengambil banyak (ukuran) dipanggil berulang kali
Ambil satu baris dari tabel menggunakan kursor. ambil satu
Langkah selanjutnya
Apa yang dilakukan kursor dengan Python?
Apa yang dilakukan kursor ()?
Bagaimana Anda membuat kursor dengan Python?
Bagaimana Anda menggunakan kursor mengeksekusi di Python?

Daftar Isi

Ambil semua baris dari tabel database menggunakan kursor's fetchall()

Sekarang, mari lihat bagaimana menggunakan

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

_9 untuk mengambil semua catatan. Untuk mengambil semua baris dari tabel database, Anda harus mengikuti langkah-langkah sederhana ini. –

Buat Koneksi basis data dari Python. Rujuk koneksi Python SQLite, koneksi Python MySQL, koneksi Python PostgreSQL
Tentukan kueri SELECT. Di sini Anda perlu mengetahui tabel dan detail kolomnya

Jalankan kueri SELECT menggunakan metode

rows = cursor.fetchmany([size=cursor.arraysize])

0

Dapatkan resultSet (semua baris) dari objek kursor menggunakan

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

5

Iterasi melalui ResultSet menggunakan
```
rows = cursor.fetchmany([size=cursor.arraysize])
```
_2 loop dan dapatkan nilai kolom dari setiap baris
Tutup koneksi database Python
Tangkap pengecualian SQL apa pun yang mungkin muncul selama proses

Mari coba ambil semua baris dari tabel

import sqlite3

def getAllRows():
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to SQLite")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchall()
        print("Total rows are:  ", len(records))
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if connection:
            connection.close()
            print("The Sqlite connection is closed")

getAllRows()

Keluaran

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

Ambil beberapa baris dari tabel menggunakan Connected to database Total rows are: 5 Printing each row Id: 1 Name: Emma Email: [email protected] Salary: 12000.0 Id: 2 Name: Scott Email: [email protected] Salary: 22000.0 Id: 3 Name: Jessa Email: [email protected] Salary: 18000.0 Id: 4 Name: Mike Email: [email protected] Salary: 13000.0 Id: 5 Name: Ricky Email: [email protected] Salary: 19000.0 The Sqlite connection is closed6

Satu hal yang saya sukai dari Python DB API adalah fleksibilitasnya. Di dunia nyata, mengambil semua baris sekaligus mungkin tidak dapat dilakukan. Jadi Python DB API memecahkan masalah ini dengan menyediakan versi berbeda dari fungsi pengambilan kelas Cursor. Versi yang paling umum digunakan adalah

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

6

Sintaks kursor

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

0

rows = cursor.fetchmany([size=cursor.arraysize])

_

Berikut ukuran adalah jumlah baris yang akan diambil. Metode ini mengambil kumpulan baris berikutnya dari hasil kueri dan mengembalikan daftar tupel. Jika tidak ada lagi baris yang tersedia, ini mengembalikan daftar kosong

Metode

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

0 kursor mengembalikan jumlah baris yang ditentukan oleh argumen ukuran. nilai defaultnya adalah 1. Jika ukuran yang ditentukan adalah 100, maka akan mengembalikan 100 baris

Mari coba ambil 3 baris dari tabel menggunakan

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

6

import sqlite3

def getlimitedRows(size):
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchmany(size)
        print("Fetching Total ", size," rows")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if (connection):
            connection.close()
            print("The Sqlite connection is closed")

getlimitedRows(3)

Keluaran

Connected to database
Fetching Total  3  rows
Printing each row

Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0

Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0

Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0

The Sqlite connection is closed

Apa yang akan terjadi jika kursor mengambil banyak (ukuran) dipanggil berulang kali

Apa yang akan terjadi jika kita memanggil

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

_6 berulang kali setelah menjalankan kueri SQL

Misalnya, kami menjalankan kueri, dan mengembalikan hasil kueri 10 baris. Selanjutnya, kami mengambil dua baris pertama menggunakan

import sqlite3

def getlimitedRows(size):
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchmany(size)
        print("Fetching Total ", size," rows")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if (connection):
            connection.close()
            print("The Sqlite connection is closed")

getlimitedRows(3)

4. Sekali lagi, kami memanggil

import sqlite3

def getlimitedRows(size):
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchmany(size)
        print("Fetching Total ", size," rows")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if (connection):
            connection.close()
            print("The Sqlite connection is closed")

getlimitedRows(3)

_4, lalu akan mengembalikan dua baris berikutnya. Mari lihat contoh untuk memahaminya dengan lebih baik

import sqlite3

def getlimitedRows(size):
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchmany(size)
        print("Fetching Total ", size, " rows")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        records = cursor.fetchmany(size)
        print("Fetching next 2 rows from table")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if connection:
            connection.close()
            print("The Sqlite connection is closed")

getlimitedRows(2)

Keluaran

Connected to database
Fetching Total  2  rows
Printing each row

Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Fetching next 2 rows from a table

Printing each row
Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


The Sqlite connection is closed

Ambil satu baris dari tabel menggunakan kursor. ambil satu

Python DB API memungkinkan kita untuk mengambil hanya satu baris. Untuk mengambil satu baris dari kumpulan hasil, kita dapat menggunakan

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

8. Metode ini mengembalikan satu tupel

Itu dapat mengembalikan tidak ada jika tidak ada baris yang tersedia di hasil.

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  [email protected]
Salary:  12000.0


Id:  2
Name:  Scott
Email:  [email protected]
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  [email protected]
Salary:  18000.0


Id:  4
Name:  Mike
Email:  [email protected]
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  [email protected]
Salary:  19000.0


The Sqlite connection is closed

8 menambah posisi kursor satu per satu dan mengembalikan baris berikutnya

Mari kita lihat contohnya sekarang

import sqlite3

def getSingleRows():
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        print("Fetching single row")
        record = cursor.fetchone()
        print(record)

        print("Fetching next row")
        record = cursor.fetchone()
        print(record)

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if connection:
            connection.close()
            print("The Sqlite connection is closed")

getSingleRows()

Keluaran

Connected to database

Fetching single row
(1, 'Emma', '[email protected]', 12000.0)

Fetching next row
(2, 'Scott', '[email protected]', 22000.0)
The Sqlite connection is closed

Langkah selanjutnya

Untuk mempraktikkan apa yang Anda pelajari di artikel ini, Selesaikan proyek Latihan Python SQLite untuk mempraktikkan operasi database

Apa yang dilakukan kursor dengan Python?

Ini adalah objek yang digunakan untuk membuat koneksi untuk mengeksekusi kueri SQL . Ini bertindak sebagai middleware antara koneksi database SQLite dan kueri SQL.

Apa yang dilakukan kursor ()?

Kursor melacak posisi di kumpulan hasil, dan memungkinkan Anda melakukan beberapa operasi baris demi baris terhadap kumpulan hasil, dengan atau tanpa kembali ke tabel asli< . Dengan kata lain, kursor secara konseptual mengembalikan kumpulan hasil berdasarkan tabel dalam database. . In other words, cursors conceptually return a result set based on tables within the databases.

Bagaimana Anda membuat kursor dengan Python?

Kelas kursor adalah instans yang dapat Anda gunakan untuk memanggil metode yang mengeksekusi pernyataan SQLite, mengambil data dari set hasil kueri. Anda dapat membuat objek Cursor menggunakan metode cursor() dari objek/kelas Connection .

Bagaimana Anda menggunakan kursor mengeksekusi di Python?

Kursor adalah objek yang membantu mengeksekusi kueri dan mengambil catatan dari database. Kursor memainkan peran yang sangat penting dalam mengeksekusi kueri. . Jenis Pengembalian

kode python