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")1 untuk berkomunikasi dengan database relasional menggunakan protokol ODBC. Setelah membaca artikel ini, Anda akan dapat menulis dan menjalankan SQL di aplikasi Python Anda. Ayo kode
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
Menghubungkan ke database — bagian kodeAda 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 pyodbcLingkungan 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 pyodbcdriver: 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")4 di artikel di bawah ini
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")5. Pertama kita menggandakan semua 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")6 kurung kurawal di kata sandi, lalu kita mengelilinginya dengan kurung kurawal lagi. Ini karena kata sandi dapat berisi beberapa karakter aneh seperti 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")7 misalnya. Mengelilingi mereka dengan kurung kurawal akan menghindari semuanya. Kami tidak ingin ada kurung kurawal yang merupakan bagian dari kata sandi kami untuk diloloskan, jadi kami menggandakannya untuk "melarikan diri dari pelarian"
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")8 sekarang diterjemahkan dengan benar ke 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")9)
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
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};"0) menyebabkan kursor menutup secara otomatis. Selain itu akan melakukan (Anda harus memasukkan password = "}}".join(password.split("}"))
constring = f"DRIVER={driver};" \
f"SERVER={host};" \
f"DATABASE={database};" \
f"UID={username};" \
f"PWD={{{password}}};" \
f"port={port};"_1; lihat contoh di bawah) apa pun yang Anda jalankan dalam blok password = "}}".join(password.split("}"))
constring = f"DRIVER={driver};" \
f"SERVER={host};" \
f"DATABASE={database};" \
f"UID={username};" \
f"PWD={{{password}}};" \
f"port={port};"2. Jika mendeteksi kesalahan, itu akan mengembalikan semua kueri. Perhatikan bahwa ini hanya berfungsi jika koneksi dibuat dengan password = "}}".join(password.split("}"))
constring = f"DRIVER={driver};" \
f"SERVER={host};" \
f"DATABASE={database};" \
f"UID={username};" \
f"PWD={{{password}}};" \
f"port={port};"3 (yang merupakan default)
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};"_4 untuk mengambil satu baris
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};"0) eksekusi akan dilakukan jika tidak terjadi kesalahan. Kursor juga ditutup otomatis
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 pyodbcdriver: 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")0
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};"6
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