Bagaimana python 3 terhubung ke database?

Dengan Python kita dapat mengotomatiskan penulisan dan eksekusi SQL. Tetapi agar ini berfungsi, kita membutuhkan Python untuk dapat berkomunikasi dengan database. Pada artikel ini kita akan fokus pada penggunaan paket yang disebut

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

Sebelum kita mulai

Mari kita tentukan ruang lingkup artikel ini

Pengguna Postgres?

Ada banyak database yang sesuai dengan ODBC dan pyodbc dan dalam artikel ini kita akan menggunakan MS SQL. Jika Anda menggunakan PostgreSQL, lihat artikel di bawah ini untuk pendekatan yang lebih optimal

Membuat koneksi Python PostgreSQL untuk pemula mutlak

Bagaimana skrip Python Anda dapat berkomunikasi dengan database Postgres Anda

mikehul. medium. com

Menghasilkan kueri SQL

Artikel ini berfokus pada pembuatan koneksi dengan database menggunakan pyodbc. Koneksi ini kemudian dapat digunakan untuk mengeksekusi SQL. Beberapa database menggunakan sintaks lain dari yang lain

• MS SQL Server
  

  import pyodbc
  
  driver: str = 'ODBC Driver 17 for SQL Server'
  username = os.environ.get("SQLSERVER_USER")
  password = os.environ.get("SQLSERVER_PASS")
  host = os.environ.get("SQLSERVER_HOST")
  port = os.environ.get("SQLSERVER_PORT")
  database = os.environ.get("SQLSERVER_DB")

  _2
• PostgreSQL
  

  import pyodbc
  
  driver: str = 'ODBC Driver 17 for SQL Server'
  username = os.environ.get("SQLSERVER_USER")
  password = os.environ.get("SQLSERVER_PASS")
  host = os.environ.get("SQLSERVER_HOST")
  port = os.environ.get("SQLSERVER_PORT")
  database = os.environ.get("SQLSERVER_DB")

  _3

Ini berarti Anda harus menulis pernyataan SQL untuk database tertentu

Ada pendekatan database-agnostik di mana Anda mendefinisikan kueri Anda dengan cara yang lebih Pythonic dan kemudian mengkompilasinya untuk database tertentu. Ini akan menghasilkan SQL khusus untuk database tersebut sehingga Anda tidak terikat dengan spesifikasi database yang sedang Anda gunakan. Ini berarti bahwa jika Anda memutuskan untuk beralih dari MySQL ke Postgres di masa mendatang, Anda tidak perlu mengubah kode Anda

Saya sedang menulis artikel ini jadi pastikan untuk mengikuti saya

Tentang pyodbc dan protokol ODBC

Sedikit latar belakang tentang paket yang kami gunakan dan cara kerjanya. Pyodbc adalah paket yang memungkinkan Anda untuk berkomunikasi dengan database (relasional). Ia menggunakan protokol Open DataBase Communication. Protokol ini menentukan bagaimana klien (seperti skrip Python Anda) dan database berkomunikasi

Anda dapat membandingkan protokol komunikasi ini dengan protokol HTTP yang memfasilitasi komunikasi antar komputer melalui internet. klien tahu cara meminta sumber daya, server tahu cara merespons, dan klien tahu seperti apa responsnya sehingga mereka dapat menggunakan informasi tersebut. Dengan cara yang sama klien dapat menggunakan protokol ODBC untuk berkomunikasi dengan database

Docker untuk pemula mutlak. perbedaan antara gambar dan wadah

Pelajari perbedaan antara gambar Docker dan wadah wadah dan gambar berbeda + contoh kode praktis

menuju ilmu data. com

Ada beberapa langkah sederhana yang akan kita lalui. Kami harus menggunakan kredensial kami untuk membuat string koneksi. Dengan string itu kita akan membuat koneksi. Pada koneksi Anda dapat membuat kursor yang akan kami gunakan untuk mengeksekusi kueri. Pertama beberapa persiapan

Persiapan. dependencies

Pertama mari kita buat lingkungan virtual dan instal satu-satunya dependensi kita

pip install pyodbc

Lingkungan virtual untuk pemula absolut — apa itu dan bagaimana membuatnya (+ contoh)

Menyelam lebih dalam ke lingkungan virtual Python, pip dan menghindari ketergantungan yang terjerat

menuju ilmu data. com

Langkah 1. Kumpulkan kredensial kami

Di sinilah kami mengambil kredensial untuk database kami. Dalam contoh di bawah ini kami menangani kredensial kami dengan cara yang paling aman. kami memuatnya dari lingkungan sehingga aplikasi kami memiliki akses tanpa melakukan hardcoding ke dalam skrip kami

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

Cegah kata sandi Anda bocor dengan mempelajari cara menerapkan

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

Amankan kode Anda dengan menggunakan variabel lingkungan dan file env

Muat file dengan aman yang berisi semua data rahasia yang diperlukan aplikasi kami seperti kata sandi, token, dll

menuju ilmu data. com

Langkah 2. Buat string koneksi

Pyodbc membutuhkan string terformat yang berisi kredensial kami untuk terhubung ke database. Dalam contoh di bawah ini kami menggunakan f-string untuk membuat string koneksi sambil menjaga kode kami tetap bersih

password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
            f"SERVER={host};" \
            f"DATABASE={database};" \
            f"UID={username};" \
            f"PWD={{{password}}};" \
            f"port={port};"

Memperbaiki kata sandi
Sesuatu yang aneh sedang terjadi dengan

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

Agak aneh tapi ternyata begini cara kerjanya. Perhatikan juga bahwa ini tidak hanya berfungsi dengan kata sandi tetapi dengan argumen apa pun, jadi jika nama pengguna Anda berisi karakter khusus, Anda juga dapat menggunakan teknik ini

String koneksi yang dihasilkan terlihat seperti ini (perhatikan bahwa

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

DRIVER=ODBC Driver 17 for SQL Server;SERVER=my_host;DATABASE=my_database;UID=my_username;PWD={my_}}password};port=my_port;

Memulai dengan Cython. Cara melakukan >1. 7 miliar perhitungan per detik dengan Python

Gabungkan kemudahan Python dengan kecepatan C

menuju ilmu data. com

Langkah 3. Menghubungkan

Ini adalah bagian yang mudah kami akan membuat koneksi (biasanya Anda memiliki satu koneksi per aplikasi). Kemudian kami membuat kursor pada koneksi. Kursor digunakan untuk iterasi pada kumpulan hasil yang dihasilkan dari kueri. Anda menutup kursor setelah selesai dengan set hasil

cnxn:pyodbc.Connection = pyodbc.connect(constring)cursor:pyodbc.Cursor = cnxn.cursor()
try:
    cursor.execute("SELECT @@VERSION")
    print(cursor.fetchone())
except Exception as e:
    print(f"Connection could not be established: {e}")
finally:
    cursor.close()

Selanjutnya kita dapat menggunakan kursor untuk mengeksekusi beberapa SQL, dalam hal ini kita mencetak versi database SQL Server kita. Jika ada yang gagal, kami mencetak kesalahan dan dalam hal apa pun kami menutup kursor kami

Ada cara yang lebih singkat dan lebih baik untuk melakukan ini

with cnxn.cursor() as cursor:
    try:
        cursor.execute("SELECT @@VERSION")
        print(cursor.fetchone())
    except Exception as e:
        print(f"Connection could not be established: {e}")

Menggunakan manajer konteks (bagian

password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
            f"SERVER={host};" \
            f"DATABASE={database};" \
            f"UID={username};" \
            f"PWD={{{password}}};" \
            f"port={port};"

password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
            f"SERVER={host};" \
            f"DATABASE={database};" \
            f"UID={username};" \
            f"PWD={{{password}}};" \
            f"port={port};"

password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
            f"SERVER={host};" \
            f"DATABASE={database};" \
            f"UID={username};" \
            f"PWD={{{password}}};" \
            f"port={port};"

password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
            f"SERVER={host};" \
            f"DATABASE={database};" \
            f"UID={username};" \
            f"PWD={{{password}}};" \
            f"port={port};"

Memahami manajer konteks Python untuk pemula mutlak

Pahami pernyataan WITH dengan lightsaber

medium. com

Bonus. Contoh kueri

Berikut beberapa contoh kueri untuk membantu Anda

Kueri 1. Memilih rekaman

Gunakan

password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
            f"SERVER={host};" \
            f"DATABASE={database};" \
            f"UID={username};" \
            f"PWD={{{password}}};" \
            f"port={port};"

with cnxn.cursor() as cursor:
    cursor.execute("SELECT * FROM INFORMATION_SCHEMA.TABLES")
    for row in cursor.fetchall():
        print(row)

SQL — Pahami cara kerja indeks untuk mempercepat kueri Anda

Tidak perlu lagi menunggu kueri lambat selesai

menuju ilmu data. com

Kueri 2. Memilih catatan;

with cnxn.cursor() as cursor:
    cursor.execute("SELECT * FROM INFORMATION_SCHEMA.TABLES")
    colnames = [col[0] for col in cursor.description]
    coltypes = [col[1] for col in cursor.description]
    for rowvalues in cursor.fetchall():
        # convert types
        rowvalues = [coltypes[i](rowvalues[i]) for i in range(len(coltypes))]
        # make dicts from the colnames and rowvalues
        row = dict(zip(colnames, rowvalues))
        print(row)

Kueri 3. Sisipan reguler

Karena kita berada di dalam blok pengelola konteks (

password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
            f"SERVER={host};" \
            f"DATABASE={database};" \
            f"UID={username};" \
            f"PWD={{{password}}};" \
            f"port={port};"

with cnxn.cursor() as cursor:
    cursor.execute("INSERT INTO med.mytable (name, age)  VALUES (?,?)", "mike", 32)

SQL — sisipkan, hapus, dan perbarui dalam SATU pernyataan. sinkronkan tabel Anda dengan MERGE

menuju ilmu data. com

Kueri 4. Memutar kembali sisipan

Dua yang pertama dimasukkan, lalu #3 gagal sehingga #1 dan #2 diputar kembali. Tidak akan ada jejak mr. a, b atau c dalam database

with cnxn.cursor() as cursor:
    cursor.execute("INSERT INTO med.mytable (name, age)  VALUES (?,?)", "mr. a", 44)
    cursor.execute("INSERT INTO med.mytable (name, age)  VALUES (?,?)", "mr. b", 33)
    cursor.execute("INSERT INTO med.mytable (name, age)  VALUES (?,?)", "mr. c", 55, "toomany")

SQL — Pelajari cara mengembalikan kueri dalam transaksi

Jalankan semua kueri atau tidak sama sekali

menuju ilmu data. com

Kueri 5. Sisipan super cepat

Sisipan di atas akan menyisipkan satu baris sekaligus. Dengan opsi di bawah ini kita dapat membuat satu pernyataan untuk banyak baris, yang sangat meningkatkan kecepatan penyisipan

import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

Baca lebih lanjut di artikel di bawah ini untuk mempelajari tentang cara kerja bagian dalam

password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
            f"SERVER={host};" \
            f"DATABASE={database};" \
            f"UID={username};" \
            f"PWD={{{password}}};" \
            f"port={port};"

Secara dramatis tingkatkan kecepatan penyisipan basis data Anda dengan pemutakhiran sederhana

4 level membuat koneksi database yang sangat cepat dengan Python

menuju ilmu data. com

Penting

Selalu pastikan untuk membersihkan variabel yang Anda masukkan ke dalam pernyataan SQL untuk mencegah serangan injeksi SQL, terutama jika Anda mengizinkan masukan pengguna ke dalam pernyataan Anda. Ini dijelaskan dengan indah oleh komik XKCD yang terkenal ini

Langkah selanjutnya

Sekarang skrip Anda dapat terhubung ke database, Anda dapat mulai menulis SQL. Anda dapat membuat API dalam 5 baris kode misalnya. Dengan cara ini Anda dapat memberi pengguna akses terkontrol ke database Anda, menentukan informasi mana yang mungkin diminta pengguna

Pastikan untuk memeriksa tautan ini untuk ikhtisar bagus dari banyak pertanyaan berguna. Terakhir, artikel di bawah ini menjelaskan cara mengimplementasikan migrasi database. Ini memungkinkan untuk merancang dan mengontrol versi basis data Anda secara terprogram

Uji dan terapkan perubahan dengan aman ke database Anda. memulai dengan Alembic

Kontrol versi database Anda dengan alat Python sederhana ini

menuju ilmu data. com

Kesimpulan

Saya berharap dapat menjelaskan cara menghubungkan ke database Anda dengan pyodbc dan menggunakannya untuk menjalankan beberapa SQL. Saya harap semuanya sejelas yang saya harapkan, tetapi jika tidak demikian, beri tahu saya apa yang dapat saya lakukan untuk mengklarifikasi lebih lanjut. Sementara itu, lihat artikel saya yang lain tentang semua jenis topik terkait pemrograman seperti ini

Bagaimana cara Python terhubung ke database eksternal?

Bagaimana cara Python terhubung ke database MS SQL?

Modul Python mana yang digunakan untuk terhubung ke database?