Python创建SQL数据库流程逐步讲解

目录
  • 前言
  • 先决条件
  • 创建脚本
  • 建立连接
  • 创建表格
  • 生成一些随机数据
  • 结论

前言

根据《2021年Stackoverflow开发者调查》,

SQL是最常用的五种编程语言之一。

所以,我们应该多投入时间来学习SQL。

由Storyset绘制的人物插图

但是有一个问题:

如何在没有数据库的情况下练习数据库查询呢?

在今天的文章中,让我们一起来解决这个基本问题,学习如何从零开始创建自己的MySQL数据库。在Python和一些外部库的帮助下,我们将创建一个简单的脚本,可以自动创建并使用随机生成的数据,填充我们的表格。

但是,在讨论实现细节之前,我们首先需要讨论一些先决条件。

注意:当然还有其他方法可以获取用于实践的SQL数据库(例如直接找资源下载),但使用Python和一些外部库可以为我们提供额外且有价值的实践机会。

先决条件

我们先从最基本的开始。

首先,需要安装MySQL Workbench并连接服务,接下来就可以开始建立数据库:

CREATE DATABASE IF NOT EXISTS your_database_name;

现在,我们只需要安装必要的python库,基本的设置就完成了。我们将要使用的库如下所示,可以通过终端轻松安装。

  1. NumPy: pip install numpy
  2. Sqlalchemy: pip install sqlalchemy
  3. Faker: pip install faker

创建脚本

完成基本设置后,我们可以开始编写python脚本了。

先用一些样板代码创建一个类,为我们提供一个蓝图,指导我们完成其余的实现。

import numpy as np
import sqlalchemy
from faker import Faker [python学习裙: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

目前我们还没用特别高级的语法。

我们基本上只是创建了一个类,存储了数据库凭据供以后使用,导入了库,并定义了一些方法。

建立连接

我们要完成的第一件事是创建一个数据库连接。

幸运的是,我们可以利用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)

这个方法可以创建并存储3个对象作为实例属性。

首先,我们创建一个连接,作为sqlalchemy应用程序的起点,描述如何与特定类型的数据库/ DBAPI组合进行对话。

在我们的例子中,我们指定一个MySQL数据库并传入我们的凭据。

接下来,创建一个连接,它可以让我们执行SQL语句和一个元数据对象(一个容器),将数据库的不同功能放在一起,让我们关联和访问数据库表。

创建表格

现在,我们需要创建数据库表。

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()

我们创建了3个表,并将它们存储在一个字典中,以供以后参考。

在sqlalchemy中创建表也非常简单。我们只需实例化一个新的表,提供表名、元数据对象,并指定不同的列。

在本例中,我们创建了一个job表、一个company表和一个person表。person表还通过了foreign kkey链接了其他表,这使数据库在实践SQL连接方面更加有趣。

定义了所有表格之后,我们只需调用MetaData对象的create_all()方法就好了。

生成一些随机数据

虽然我们创建了数据库表,但仍然没有任何数据可用。因此,我们需要生成一些随机数据并将其插入到表中。

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)

现在,我们可以利用Faker库来生成随机数据。

我们只需在for循环中使用随机生成的数据,创建一个由字典表示的新记录。然后将单个记录追加到可用于(多个)insert语句的列表中。

接下来,从连接对象中调用execute()方法,并将字典列表作为参数传递。

就是这样!我们成功实现了类—只需要把类实例化,并调用相关函数来创建数据库。

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

试着做一个查询

剩下的唯一一件事是——需要验证我们的数据库是否已经启动和运行,是否确实包含一些数据。

从基本的查询开始:

SELECT *
FROM jobs
LIMIT 10;

基本查询结果[图片by作者]

看起来我们的脚本成功了,我们有一个包含实际数据的数据库。

现在,尝试一个更复杂的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;

这个结果看起来很靠谱 – 可以说我们的数据库在正常运行。

结论

在本文中,我们学习了如何利用Python和一些外部库来用随机生成的数据创建我们自己的实践数据库。

虽然可以很容易地下载现有的数据库来开始练习SQL,但使用Python从头创建自己的数据库提供了额外的学习机会。由于SQL和Python经常紧密联系在一起,所以这些学习机会可能会特别有用。

到此这篇关于Python创建SQL数据库流程逐步讲解的文章就介绍到这了,更多相关Python创建SQL内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2022-09-22

Python操作SQLite数据库的方法详解【导入,创建,游标,增删改查等】

本文实例讲述了Python操作SQLite数据库的方法.分享给大家供大家参考,具体如下: SQLite简介 SQLite,是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中.它是D.RichardHipp建立的公有领域项目.它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了.它能够支持Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如 Tcl.C

Python 中创建 PostgreSQL 数据库连接池

目录 习惯于使用数据库之前都必须创建一个连接池,即使是单线程的应用,只要有多个方法中需用到数据库连接,建立一两个连接的也会考虑先池化他们.连接池的好处多多, 1) 如果反复创建连接相当耗时, 2) 对于单个连接一路用到底的应用,有连接池时避免了数据库连接对象传来传去, 3) 忘记关连接了,连接池幸许还能帮忙在一定时长后关掉,当然密集取连接的应用势将耗尽连接, 4) 一个应用打开连接的数量是可控的 接触到 Python 后,在使用 PostgreSQL 也自然而然的考虑创建连接池,使用时从池中取,

对Python中创建进程的两种方式以及进程池详解

在Python中创建进程有两种方式,第一种是: from multiprocessing import Process import time def test(): while True: print('---test---') time.sleep(1) if __name__ == '__main__': p=Process(target=test) p.start() while True: print('---main---') time.sleep(1) 上面这段代码是在window

Python 如何创建一个线程池

问题 你创建一个工作者线程池,用来响应客户端请求或执行其他的工作. 解决方案 concurrent.futures 函数库有一个 ThreadPoolExecutor 类可以被用来完成这个任务. 下面是一个简单的TCP服务器,使用了一个线程池来响应客户端: from socket import AF_INET, SOCK_STREAM, socket from concurrent.futures import ThreadPoolExecutor def echo_client(sock, c

Python中创建字典的几种方法总结(推荐)

1.传统的文字表达式: >>> d={'name':'Allen','age':21,'gender':'male'} >>> d {'age': 21, 'name': 'Allen', 'gender': 'male'} 如果你可以事先拼出整个字典,这种方式是很方便的. 2.动态分配键值: >>> d={} >>> d['name']='Allen' >>> d['age']=21 >>> d[

Python中创建二维数组

二维数组 二维数组本质上是以数组作为数组元素的数组,即"数组的数组",类型说明符 数组名[常量表达式][常量表达式].二维数组又称为矩阵,行列数相等的矩阵称为方阵.对称矩阵a[i][j] = a[j][i],对角矩阵:n阶方阵主对角线外都是零元素. Python中创建二维数组 Python中的列表list可以当做一维数组使用,但是没有直接的定义使用二维数组.如果直接使用a = [][]会产生SyntaxError: invalid syntax语法不正确错误. 一般Python中创建二

在python中创建指定大小的多维数组方式

python中创建指定大小的二维数组,有点像C++中进行动态申请内存创建数组,不过相比较而言,python中更为简单一些. 创建n行m列的二维数组: n = 2 m = 3 matrix = [None]*2 for i in range(len(matrix)): matrix[i] = [0]*3 print(matrix) 当然也可以使用list comprehension的方式创建: n = 2 m = 3 matrix = [[0]*m for i in range(n)] print

Java中常用的数据库连接池_动力节点Java学院整理

定义 数据库连接是一种关键的有限的昂贵的资源,这一点在多用户的网页应用程序中体现得尤为突出.对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性,影响到程序的性能指标.数据库连接池正是针对这个问题提出来的. 数据库连接池负责分配.管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个:释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏.这项技术能明显提高对数据库操作的性能. 参考资料 DBCP 下载地址:http://

Python实现Mysql数据库连接池实例详解

python连接Mysql数据库: Python编程中可以使用MySQLdb进行数据库的连接及诸如查询/插入/更新等操作,但是每次连接MySQL数据库请求时,都是独立的去请求访问,相当浪费资源,而且访问数量达到一定数量时,对mysql的性能会产生较大的影响.因此,实际使用中,通常会使用数据库的连接池技术,来访问数据库达到资源复用的目的. 数据库连接池 python的数据库连接池包 DBUtils: DBUtils是一套Python数据库连接池包,并允许对非线程安全的数据库接口进行线程安全包装.D

详解python中的线程与线程池

线程 进程和线程 什么是进程? 进程就是正在运行的程序, 一个任务就是一个进程, 进程的主要工作是管理资源, 而不是实现功能 什么是线程? 线程的主要工作是去实现功能, 比如执行计算. 线程和进程的关系就像员工与老板的关系, 老板(进程) 提供资源 和 工作空间, 员工(线程) 负责去完成相应的任务 特点 一个进程至少由一个线程, 这一个必须存在的线程被称为主线程, 同时一个进程也可以有多个线程, 即多线程 当我们我们遇到一些需要重复执行的代码时, 就可以使用多线程分担一些任务, 进而加快运行速

Java实现数据库连接池的方法

本文实例讲述了Java实现数据库连接池的方法.分享给大家供大家参考.具体如下: package com.kyo.connection; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.Driver; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import j

Spring Boot集成Druid数据库连接池

1. 前言 Druid数据库连接池由阿里巴巴开源,号称是java语言中最好的数据库连接池,是为监控而生的.Druid的官方地址是:https://github.com/alibaba/druid 通过本文,我们可以看到 Spring Boot 如何配置数据源 Spring Boot 如何集成Druid数据库连接池 如何打开并访问Druid数据库连接池的监控功能 Spring Boot 使用JdbcTemplate操作数据库 2. 配置pom.xml <parent> <groupId&g