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

更新時(shí)間：2022年09月22日 10:21:22 作者：螞蟻愛(ài)Python

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

前言

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

SQL是最常用的五種編程語(yǔ)言之一。

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

由Storyset繪制的人物插圖

但是有一個(gè)問(wèn)題：

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

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

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

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

先決條件

我們先從最基本的開(kāi)始。

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

CREATE DATABASE IF NOT EXISTS your_database_name;

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

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

創(chuàng)建腳本

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

先用一些樣板代碼創(chuàng)建一個(gè)類，為我們提供一個(gè)藍(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

目前我們還沒(méi)用特別高級(jí)的語(yǔ)法。

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

建立連接

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

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

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)

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

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

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

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

創(chuàng)建表格

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

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個(gè)表，并將它們存儲(chǔ)在一個(gè)字典中，以供以后參考。

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

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

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

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

雖然我們創(chuàng)建了數(shù)據(jù)庫(kù)表，但仍然沒(méi)有任何數(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庫(kù)來(lái)生成隨機(jī)數(shù)據(jù)。

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

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

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

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

試著做一個(gè)查詢

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

從基本的查詢開(kāi)始：

SELECT *
FROM jobs
LIMIT 10;

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

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

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

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;

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

結(jié)論

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

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

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

您可能感興趣的文章: