Python創(chuàng)建SQL數(shù)據(jù)庫流程逐步講解

更新時間：2022年09月22日 10:21:22 作者：螞蟻愛Python

會寫SQL很重要，能高效地查詢數(shù)據(jù)庫被認(rèn)為是數(shù)據(jù)分析師/科學(xué)家最基本的技能之一。SQL不僅重要，而且非常常用，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧

前言

根據(jù)《2021年Stackoverflow開發(fā)者調(diào)查》，

SQL是最常用的五種編程語言之一。

所以，我們應(yīng)該多投入時間來學(xué)習(xí)SQL。

由Storyset繪制的人物插圖

但是有一個問題：

如何在沒有數(shù)據(jù)庫的情況下練習(xí)數(shù)據(jù)庫查詢呢？

在今天的文章中，讓我們一起來解決這個基本問題，學(xué)習(xí)如何從零開始創(chuàng)建自己的MySQL數(shù)據(jù)庫。在Python和一些外部庫的幫助下，我們將創(chuàng)建一個簡單的腳本，可以自動創(chuàng)建并使用隨機(jī)生成的數(shù)據(jù)，填充我們的表格。

但是，在討論實(shí)現(xiàn)細(xì)節(jié)之前，我們首先需要討論一些先決條件。

注意：當(dāng)然還有其他方法可以獲取用于實(shí)踐的SQL數(shù)據(jù)庫(例如直接找資源下載)，但使用Python和一些外部庫可以為我們提供額外且有價值的實(shí)踐機(jī)會。

先決條件

我們先從最基本的開始。

首先，需要安裝MySQL Workbench并連接服務(wù)，接下來就可以開始建立數(shù)據(jù)庫：

CREATE DATABASE IF NOT EXISTS your_database_name;

現(xiàn)在，我們只需要安裝必要的python庫，基本的設(shè)置就完成了。我們將要使用的庫如下所示，可以通過終端輕松安裝。

NumPy： pip install numpy
Sqlalchemy： pip install sqlalchemy
Faker： pip install faker

創(chuàng)建腳本

完成基本設(shè)置后，我們可以開始編寫python腳本了。

先用一些樣板代碼創(chuàng)建一個類，為我們提供一個藍(lán)圖，指導(dǎo)我們完成其余的實(shí)現(xiàn)。

import numpy as np
import sqlalchemy
from faker import Faker [python學(xué)習(xí)裙：90 3971231###
from sqlalchemy import Table, Column, Integer, String, MetaData, Date,
class SQLData:
    def __init__(self, server:str, db:str, uid:str, pwd:str) -> None:
        self.__fake = Faker()
        self.__server = server
        self.__db = db
        self.__uid = uid
        self.__pwd = pwd
        self.__tables = dict()
    def connect(self) -> None:
        pass
    def drop_all_tables(self) -> None:
        pass
    def create_tables(self) -> None:
        pass
    def populate_tables(self) -> None:
        pass

目前我們還沒用特別高級的語法。

我們基本上只是創(chuàng)建了一個類，存儲了數(shù)據(jù)庫憑據(jù)供以后使用，導(dǎo)入了庫，并定義了一些方法。

建立連接

我們要完成的第一件事是創(chuàng)建一個數(shù)據(jù)庫連接。

幸運(yùn)的是，我們可以利用python庫sqlalchemy來完成大部分工作。

class SQLData:
    #...
    def connect(self) -> None:
        self.__engine = sqlalchemy.create_engine(
            f"mysql+pymysql://{self.__uid}:{self.__pwd}@{self.__server}/{self.__db}"
        )
        self.__conn = self.__engine.connect()
        self.__meta = MetaData(bind=self.__engine)

這個方法可以創(chuàng)建并存儲3個對象作為實(shí)例屬性。

首先，我們創(chuàng)建一個連接，作為sqlalchemy應(yīng)用程序的起點(diǎn)，描述如何與特定類型的數(shù)據(jù)庫/ DBAPI組合進(jìn)行對話。

在我們的例子中，我們指定一個MySQL數(shù)據(jù)庫并傳入我們的憑據(jù)。

接下來，創(chuàng)建一個連接，它可以讓我們執(zhí)行SQL語句和一個元數(shù)據(jù)對象(一個容器)，將數(shù)據(jù)庫的不同功能放在一起，讓我們關(guān)聯(lián)和訪問數(shù)據(jù)庫表。

創(chuàng)建表格

現(xiàn)在，我們需要創(chuàng)建數(shù)據(jù)庫表。

class SQLData:
    #...
    def create_tables(self) -> None:
        self.__tables['jobs'] = Table (
            'jobs', self.__meta,
            Column('job_id', Integer, primary_key=True, autoincrement=True, nullable=False),
            Column('description', String(255))
        )
        self.__tables['companies'] = Table(
            'companies', self.__meta,
            Column('company_id', Integer, primary_key=True, autoincrement=True, nullable=False),
            Column('name', String(255), nullable=False),
            Column('phrase', String(255)),
            Column('address', String(255)),
            Column('country', String(255)),
            Column('est_date', Date)
        )
        self.__tables['persons'] = Table(
            'persons', self.__meta,
            Column('person_id', Integer, primary_key=True, autoincrement=True, nullable=False),
            Column('job_id', Integer, ForeignKey('jobs.job_id'), nullable=False),
            Column('company_id', Integer, ForeignKey('companies.company_id'), nullable=False),
            Column('last_name', String(255), nullable=False),
            Column('first_name', String(255)),
            Column('date_of_birth', Date),
            Column('address', String(255)),
            Column('country', String(255)),
            Column('zipcode', String(10)),
            Column('salary', Integer)
        )
        self.__meta.create_all()

我們創(chuàng)建了3個表，并將它們存儲在一個字典中，以供以后參考。

在sqlalchemy中創(chuàng)建表也非常簡單。我們只需實(shí)例化一個新的表，提供表名、元數(shù)據(jù)對象，并指定不同的列。

在本例中，我們創(chuàng)建了一個job表、一個company表和一個person表。person表還通過了foreign kkey鏈接了其他表，這使數(shù)據(jù)庫在實(shí)踐SQL連接方面更加有趣。

定義了所有表格之后，我們只需調(diào)用MetaData對象的create_all()方法就好了。

生成一些隨機(jī)數(shù)據(jù)

雖然我們創(chuàng)建了數(shù)據(jù)庫表，但仍然沒有任何數(shù)據(jù)可用。因此，我們需要生成一些隨機(jī)數(shù)據(jù)并將其插入到表中。

class SQLData:
    #...
    def populate_tables(self) -> None:
        jobs_ins = list()
        companies_ins = list()
        persons_ins = list()
        for _ in range(100):
            record = dict()
            record['description'] = self.__fake.job()
            jobs_ins.append(record)
        for _ in range(100):
            record = dict()
            record['name'] = self.__fake.company()
            record['phrase'] = self.__fake.catch_phrase()
            record['address'] = self.__fake.street_address()
            record['country'] = self.__fake.country()
            record['est_date'] = self.__fake.date_of_birth()
            companies_ins.append(record)
        for _ in range(500):
            record = dict()
            record['job_id'] = np.random.randint(1, 100)
            record['company_id'] = np.random.randint(1, 100)
            record['last_name'] = self.__fake.last_name()
            record['first_name'] = self.__fake.first_name()
            record['date_of_birth'] = self.__fake.date_of_birth()
            record['address'] = self.__fake.street_address()
            record['country'] = self.__fake.country()
            record['zipcode'] = self.__fake.zipcode()
            record['salary'] = np.random.randint(60000, 150000)
            persons_ins.append(record)
        self.__conn.execute(self.__tables['jobs'].insert(), jobs_ins)
        self.__conn.execute(self.__tables['companies'].insert(), companies_ins)
        self.__conn.execute(self.__tables['persons'].insert(), persons_ins)

現(xiàn)在，我們可以利用Faker庫來生成隨機(jī)數(shù)據(jù)。

我們只需在for循環(huán)中使用隨機(jī)生成的數(shù)據(jù)，創(chuàng)建一個由字典表示的新記錄。然后將單個記錄追加到可用于(多個)insert語句的列表中。

接下來，從連接對象中調(diào)用execute()方法，并將字典列表作為參數(shù)傳遞。

就是這樣！我們成功實(shí)現(xiàn)了類—只需要把類實(shí)例化，并調(diào)用相關(guān)函數(shù)來創(chuàng)建數(shù)據(jù)庫。

if __name__ == '__main__':
    sql = SQLData('localhost','yourdatabase','root','yourpassword')
    sql.connect()
    sql.create_tables()
    sql.populate_tables()

試著做一個查詢

剩下的唯一一件事是——需要驗(yàn)證我們的數(shù)據(jù)庫是否已經(jīng)啟動和運(yùn)行，是否確實(shí)包含一些數(shù)據(jù)。

從基本的查詢開始：

SELECT *
FROM jobs
LIMIT 10;

基本查詢結(jié)果[圖片by作者]

看起來我們的腳本成功了，我們有一個包含實(shí)際數(shù)據(jù)的數(shù)據(jù)庫。

現(xiàn)在，嘗試一個更復(fù)雜的SQL語句：

SELECT
  p.first_name,
  p.last_name,
  p.salary,
  j.description
FROM
  persons AS p
JOIN
  jobs AS j ON
  p.job_id = j.job_id
WHERE
  p.salary > 130000
ORDER BY
  p.salary DESC;

這個結(jié)果看起來很靠譜 – 可以說我們的數(shù)據(jù)庫在正常運(yùn)行。

結(jié)論

在本文中，我們學(xué)習(xí)了如何利用Python和一些外部庫來用隨機(jī)生成的數(shù)據(jù)創(chuàng)建我們自己的實(shí)踐數(shù)據(jù)庫。

雖然可以很容易地下載現(xiàn)有的數(shù)據(jù)庫來開始練習(xí)SQL，但使用Python從頭創(chuàng)建自己的數(shù)據(jù)庫提供了額外的學(xué)習(xí)機(jī)會。由于SQL和Python經(jīng)常緊密聯(lián)系在一起，所以這些學(xué)習(xí)機(jī)會可能會特別有用。

到此這篇關(guān)于Python創(chuàng)建SQL數(shù)據(jù)庫流程逐步講解的文章就介紹到這了,更多相關(guān)Python創(chuàng)建SQL內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: