创建本地数据库mysql+python调用本地数据库

輕栀

已于 2024-10-19 19:26:25 修改

阅读量729

点赞数 1

文章标签：数据库

于 2023-08-20 14:10:32 首次发布

本文链接：https://blog.csdn.net/mnwl12_0/article/details/132391239

版权

dbeaver下载网速感人，可以从镜像站中下载：
DBeaver - Browse Files at SourceForge.net

1.到对应官网下载mysql压缩包：

mysql官网下载地址：https://dev.mysql.com/downloads/mysql/点击打开链接

删除数据库中值重复的行：

DELETE FROM table_name
WHERE column_name IN (
  SELECT column_name
  FROM table_name
  GROUP BY column_name
  HAVING COUNT(*) > 1
)

【切记不要下载错，第一个是MSI，这一款是创建客户端版本。后面也会谈到。，下载ZIP版本】

首先安装包解压后，没有网上教程里面提到的data文件夹和my-default.ini，如下图所示

配置环境变量请参照网上教程，这个一般没有问题，即在环境变量中Path中加入MYSQL解压包bin所在路径，步骤如下：

打开命令行cmd窗口，输入 sysdm.cpl，找到高级—环境变量，对应系统变量，找到path，点击编辑，添加MYSQL解压包bin所在路径，C:\Users\59980\MYSQL\mysql-8.1.0-winx64\bin

主要注意以下几点：

（1）my.ini文件存不存在不重要，data文件夹存不存在也不重要，关键需要自己在根目录下新建一个my.ini文件，具体内容如下：

----------------------------------------------my.ini的内容-------------------------------------

[mysqld]
#设置mysql客户端默认字符集
default-character-set=utf8
#设置端口号
port=3306
#设置mysql的安装目录
#切记此处一定要用双斜杠\
basedir=C:\\Program Files\\MySQL\\mysql-8.1.0-winx64
#允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
#允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

----------------------------------------------my.ini的内容-------------------------------------

创建好后应该是显示如下页面：my.ini是配置文件，而不是文本文件，

详细过程是：在bin文件夹的上一级文件夹里创建一个名为my.ini文本文件，把上面内容粘贴进去，【切记路径要改】，然后把修改好的文件另存在bin文件夹，格式为所有文件，文件名不变，这样就是配置文件，而不是文本文件，如下图：

可以直接复制粘贴，主要修改下basedir后的路径，注意路径中为\\而非\

（2）请不要自己创建data文件夹

网上很多教程建议自己新建一个空data文件夹，再在my.ini文件中增加一句：

# 设置mysql数据库的数据的存放目录
datadir=D:\\softnew\\MYSQL\\mysql-5.7.20-winx64\\data

这样会导致服务无法启动，不要添加这一句，也不要自己新建data文件夹，由mysql自动生成data就好，具体来说：

“凡是出现**.dll加载失败，找不到**.dll等现象，都是由于底层c++模块组件缺失，需要你更新系统模块，保持是最新的（到系统更新那里查看最新更新），不需要去找软件修复dll组件，等待就是最好的答案。”

1）以管理员的方式打开cmd命令窗口（直接打开cmd运行，可能会报错），并且进入到mysql安装目录的bin目录下。然后输入命令mysqld install 显示成功

2）再输入：mysqld --initialize 此时应该没有任何提示，在这里会自动生成data文件夹

输入：mysqld --initialize-insecure --user=mysql

3）再输入：net start mysql 显示

（net stop mysql 停止SQL服务）

4）最后输入：mysql -u root -p会显示让你输入密码，直接回车即可，如果直接回车不行，可以看下文【另注】：查看密码，就成功进入到了数据库。

最后data文件夹里面的内容应该显示为：

另注：第一次登陆MYSQL时，会提示要求输入初始密码，这是考虑安全因素，命令：mysqld --initialize会随机生成密码。

初始密码在上图data文件夹下的xxx.err文件中，可以用记事本打开，用ctrl+f 查找功能找到如下一行记录：

[Note] A temporary password is generated for root@localhost: NZ+uhXPq1zN.

其中NZ+uhXPq1zN.即为初始密码（注意.号不要漏了）

改密码也很简单：进入sql后输入：

ALTER USER 'root'@'localhost' IDENTIFIED BY ' '; ' '里面是你想要修改的密码

按照上述步骤一定可以成功创建本地数据库，完成上述操作后进入数据库输入密码时重新以管理员方式运行cmd，不要在bin文件夹里去输入密码登录，其次，最好先找到密码用密码登陆上去，再修改密码。

结果如下图：

此时登录就需要密码了，具体验证可以退出当前命令窗口，然后重新进入数据库，不输入密码直接回车会出现错误命令，输入之前设置的密码登录参能进入当前数据库。如下图：

另外如果出现

这种情况，按以下步骤操作：进入bin文件夹，输入

cd C:\Users\59980\mysql-8.1.0-winx64\bin

C:\Users\59980\mysql-8.1.0-winx64\bin>mysqld --console

此时会出现一系列信息，不要关闭这个窗口，另外再以管理员身份打开一个新的cmd窗口输入：

C:\Windows\System32>cd C:\Users\59980\mysql-8.1.0-winx64\bin

C:\Users\59980\mysql-8.1.0-winx64\bin>mysql -u root -p

然后到data里面找到后缀err文件找到密码，输入就可以进入数据库里：

创建好后就可以使用数据库了：

后期如果出现ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)

处理步骤如下：

1、登录到安装Mysql的主机，打开cmd命令行工具，执行命令mysql -uroot -p，报出下面所描述的错误：

解决方案:

1、首先以管理员身份启动cmd，要不然服务禁止访问。

2、然后在cmd中输入 mysqld --remove mysql或者mysqld --remove mysql57来移除服务。

3、然后进入MySQL安装目录中找到data文件，清空其中全部文件。我的目录是D:\MySQL\MySQL Server 5.7\data，没有data文件自己建一个。（不要清空，所有运行的文件都存储在文件夹中，删除后需要重新整理庞大的数据，不清空执行下面的步骤后依然可以用原来密码登录）。

4、接下来注册服务，mysqld -install。

5、然后开始初始化，mysqld --initialize，然后可以看到data文件夹中文件正在初始化中（生成文件中。。。）。

6、初始化过程中会把登录密码初始化，可以在data文件中找到以.err结尾的文件，打开

7、找到初始化的密码记下来

8、启动MySQL服务，cmd中输入net start mysql57或者net start mysql，启动成功

9、登录使用初始化密码，修改密码（步骤上面有），打完收工

如果是在D盘：先输入D:

进入到D盘

如果显示dll模块缺失，无法运行代码，带我的网盘里面dll工具包下载微软常用运行库合集_2019.07.20_X64.exe这个东西，安装即可解决，还有这两个东西一起下载安装了：

Download Microsoft .NET Framework 4.6.1（Web 安装程序） from Official Microsoft Download Center

Download Visual C++ Redistributable for Visual Studio 2015 from Official Microsoft Download Center

以下部分再管理员授权下的cmd里面运行：

显示已有的数据库：

#这条SQL语句必会，因为使用oracle数据库或者DBeaver数据库连接时会用到选择哪一个数据库：

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.08 sec)

还有查看数据库的端口的SQL语句也要必会：

show variables like 'port';

会显示当前数据库的端口：

创建新的数据库：

mysql> CREATE DATABASE lite;
Query OK, 1 row affected (0.19 sec)

使用数据库：

mysql> USE lite;
Database changed

执行完以上操作后就可以在DB里面链接本地数据库了。

建表：

建表执行：

mysql> CREATE TABLE t_user (user_id BIGINT NOT NULL, username VARCHAR(20));
Query OK, 0 rows affected (0.82 sec)

查看表：

查看数据库中的所有表：

mysql> SHOW TABLES;
+----------------+
| Tables_in_lite |
+----------------+
| t_user         |
+----------------+
1 row in set (0.00 sec)

查看表的详情：

mysql> DESCRIBE t_user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| user_id  | bigint(20)  | NO   |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入数据：

mysql> INSERT INTO t_user(user_id, username) VALUES(1, '老卫');
Query OK, 1 row affected (0.08 sec)

删除表：

drop TABLE t_user;
Query OK, 0 rows affected (0.05 sec)

其他操作和oracle数据库差不多。

更多对本地数据库的操作可以参考：

mysql查看数据库信息常用命令_Micaelyu的博客-CSDN博客

在数据库里面创建一个新表：

create table first(id int);

id int 一定要加，不能创建一列都没有的空表。

--------------------------------用python 调用本地数据库------------------------------------

调出CMD窗口，输入

pip install PyMySQL

pip install mysql-connector

pip install mysql

回车

运行结果如下则安装成功。

[以下页面为管理员的CMD页面，进入到SQL操作]

在上面的mysql数据库里面新建一个表t_user；然后插入一些数据以便后续使用，

show tables;

CREATE TABLE t_user (user_id BIGINT NOT NULL, username VARCHAR(20));

INSERT INTO t_user(user_id, username) VALUES(1,'老卫');

INSERT INTO t_user(user_id, username) VALUES(2,'老王');

回到正常CMD页面，输入python ,进入python:

这个时候依旧不能操作的，不然报错，为了避免后续出错，这里我们提前把python 连接数据库验证方式改为：mysql_native_password连接方式，如果不改会出现不兼容问题：

NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported。

下面这行代码在mysql里面运行：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '152617';

这个时候你的数据库里面有两条信息，现在正式进入python,这里采用VScode来编写，不用命令行方式：在vscode里面新建一个test.ipynb文件，敲入以下代码：

import mysql.connector
import pymysql
conn=pymysql.connect(host = '127.0.0.1' # 连接名称，默认127.0.0.1
,user = 'root' # 用户名
,password='152617' # 密码
,port= 3306 # 端口，默认为3306
,db='lite' # 数据库名称
,charset='utf8' # 字符编码
)
cur = conn.cursor() # 生成游标对象
sql="select * from `t_user` " # SQL语句
cur.execute(sql) # 执行SQL语句
data = cur.fetchall() # 通过fetchall方法获得数据
for i in data[:2]: # 打印输出前2条数据
    print(i)
cur.close() # 关闭游标
conn.close() # 关闭连接
# engine = sql.create_engine('mysql+mysqlconnector://user:password@localhost:port/database?auth_plugin=mysql_native_password')

输出结果为：

在此如果出现报错：keyerror:255；

出现这个是因为MySQL8.0及以上得版本更新了很多字符集，但是这些字符集长度超过255了，所以旧版的PyMySQL不支持长度超过255的字符。。。 所以解决办法就是：
更新PyMySQL即可解决。

在命令行CMD里面输入：

pip install -upgrade pymysql

Python对MySql数据库实现增删改查

增：

import pymysql
#连接数据库
conn=pymysql.connect(host = '127.0.0.1' # 连接名称，默认127.0.0.1
,user = 'root' # 用户名
,passwd='152617' # 密码
,port= 3306 # 端口，默认为3306
,db='lite' # 数据库名称
,charset='utf8' # 字符编码
)
cur = conn.cursor() # 生成游标对象
#=============插入语句===============================
sql1= "INSERT INTO t_user VALUES ('4','魏六')"
#===================================================
try:
    cur.execute(sql1) # 执行插入的sql语句
    conn.commit() # 提交到数据库执行
except:
    conn.rollback()# 如果发生错误则回滚

conn.close() # 关闭数据库连接

import mysql.connector
import pymysql
conn=pymysql.connect(host = '127.0.0.1' # 连接名称，默认127.0.0.1
,user = 'root' # 用户名
,password='152617' # 密码
,port= 3306 # 端口，默认为3306
,db='lite' # 数据库名称
,charset='utf8' # 字符编码
)
cur = conn.cursor() # 生成游标对象
sql="select * from `t_user` " # SQL语句
cur.execute(sql) # 执行SQL语句
data = cur.fetchall() # 通过fetchall方法获得数据
for i in data[:]: # 打印输出前2条数据
    print(i)
cur.close() # 关闭游标
conn.close() # 关闭连接
# engine = sql.create_engine('mysql+mysqlconnector://user:password@localhost:port/database?auth_plugin=mysql_native_password')

对应的结果为：

看一下数据库里面的内容：

删：

import pymysql
#连接数据库
conn=pymysql.connect(host = '127.0.0.1' # 连接名称，默认127.0.0.1
,user = 'root' # 用户名
,passwd='152617' # 密码
,port= 3306 # 端口，默认为3306
,db='lite' # 数据库名称
,charset='utf8' # 字符编码
)
cur = conn.cursor() # 生成游标对象
#=============删除语句===============================
sql = "DELETE FROM t_user WHERE `user_id` = 1"
#===================================================
try:
    cur.execute(sql) # 执行插入的sql语句
    conn.commit() # 提交到数据库执行
except:
    conn.rollback()# 如果发生错误则回滚

conn.close() # 关闭数据库连接

下面再看一下数据库有啥：

改：

sql ="UPDATE t_user SET `user_id` = '1.3' WHERE `username` = '老王' "

其他地方不变：结果如下：

MYSQL创建表格基本句式：

CREATE TABLE bond_transaction_daily_1 ( ID INT, TRADE_DATE DATE, TRADE_CODE VARCHAR(50), BOND_NAME VARCHAR(50), ISSUER VARCHAR(50), REMAINING_TERM DECIMAL(10,2), TRADE_SOURCE VARCHAR(50), WEIGHTED_YTM DECIMAL(10,2), WEIGHTED_YTE DECIMAL(10,2), WEIGHTED_YIELD DECIMAL(10,2), WEIGHTED_PRICE DECIMAL(10,2), TRADE_COUNT INT, TRADE_VOLUME INT, TRADE_AMOUNT DECIMAL(10,2), CLOSING_YTM DECIMAL(10,2), CLOSING_YTE DECIMAL(10,2), CLOSING_YIELD_WIND DECIMAL(10,2), CLOSING_DEVIATION DECIMAL(10,2), CLOSING_MARKET_PRICE DECIMAL(10,2), DURATION DECIMAL(10,2), CONVEXITY DECIMAL(10,2), MAX_YIELD DECIMAL(10,2), MIN_YIELD DECIMAL(10,2), WEIGHTED_AVERAGE_YIELD DECIMAL(10,2), OPENING_YIELD DECIMAL(10,2), CLOSING_YIELD DECIMAL(10,2), MAX_CLEAN_PRICE DECIMAL(10,2), MIN_CLEAN_PRICE DECIMAL(10,2), WEIGHTED_AVERAGE_CLEAN_PRICE DECIMAL(10,2), OPENING_CLEAN_PRICE DECIMAL(10,2), CLOSING_CLEAN_PRICE DECIMAL(10,2), MAX_FULL_PRICE DECIMAL(10,2), MIN_FULL_PRICE DECIMAL(10,2), WEIGHTED_AVERAGE_FULL_PRICE DECIMAL(10,2), OPENING_FULL_PRICE DECIMAL(10,2), CLOSING_FULL_PRICE DECIMAL(10,2), ISSUE_COUPON_RATE DECIMAL(10,2), INTEREST_RATE_TYPE VARCHAR(50), BOND_RATING VARCHAR(50), ISSUER_RATING VARCHAR(50), SPECIAL_TERMS VARCHAR(50), BOND_TYPE VARCHAR(50), MARKET VARCHAR(50), ISSUE_DATE DATE );

oracle数据库中创建表句式：

CREATE TABLE bond_transaction_daily_1 ( ID NUMBER, TRADE_DATE DATE, TRADE_CODE VARCHAR2(50), BOND_NAME VARCHAR2(50), ISSUER VARCHAR2(50), REMAINING_TERM NUMBER, TRADE_SOURCE VARCHAR2(50), WEIGHTED_YTM NUMBER, WEIGHTED_YTE NUMBER, WEIGHTED_YIELD NUMBER, WEIGHTED_PRICE NUMBER, TRADE_COUNT NUMBER, TRADE_VOLUME NUMBER, TRADE_AMOUNT NUMBER, CLOSING_YTM NUMBER, CLOSING_YTE NUMBER, CLOSING_YIELD_WIND NUMBER, CLOSING_DEVIATION NUMBER, CLOSING_MARKET_PRICE NUMBER, DURATION NUMBER, CONVEXITY NUMBER, MAX_YIELD NUMBER, MIN_YIELD NUMBER, WEIGHTED_AVERAGE_YIELD NUMBER, OPENING_YIELD NUMBER, CLOSING_YIELD NUMBER, MAX_CLEAN_PRICE NUMBER, MIN_CLEAN_PRICE NUMBER, WEIGHTED_AVERAGE_CLEAN_PRICE NUMBER, OPENING_CLEAN_PRICE NUMBER, CLOSING_CLEAN_PRICE NUMBER, MAX_FULL_PRICE NUMBER, MIN_FULL_PRICE NUMBER, WEIGHTED_AVERAGE_FULL_PRICE NUMBER, OPENING_FULL_PRICE NUMBER, CLOSING_FULL_PRICE NUMBER, ISSUE_COUPON_RATE NUMBER, INTEREST_RATE_TYPE VARCHAR2(50), BOND_RATING VARCHAR2(50), ISSUER_RATING VARCHAR2(50), SPECIAL_TERMS VARCHAR2(50), BOND_TYPE VARCHAR2(50), MARKET VARCHAR2(50), ISSUE_DATE DATE );