ModuleNotFoundError: No module named ‘DBUtils’

When writing database connections in Python, you use a data connection pool, hence the idea of DBUtils, a simple PIP install DBUtile

Write code to test it after installation

from DBUtils.PooledDB import PooledDB
Traceback (most recent call last):
File "<stdin>", line 1.in <module>
ImportError: No module named DBUtils.PooledDB
Copy the code

Unexpectedly error ！！！！！

PIP List check it out

It’s already installed

Version 2.0 May be version-induced

Check out the manual on the official website

Pypi.org/project/DBU…

Webwareforpython. Making. IO/DBUtils/mai…

The reason:

Sure enough, 2.0 is written

from dbutils.pooled_db import PooledDB

The way I write it (1.3)

from DBUtils.PooledDB import PooledDB

Solutions:

  • Use from dbutils.pooled_db import PooledDB

  • PIP install DBUtils==1.3 Reinstall version 1.3

MySQL source is slow to import SQL files

It took a long time for the blogger to complete more than 1 million pieces of data. After checking mysql configuration, it can be completed in a few minutes.

Mysql -u root -p -h 127.0.0.1 mysql>use test;
Database changed

mysql> set global innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.03 sec)

mysql> set global max_allowed_packet=1024*1024*20;
Query OK, 0 rows affected (0.00 sec)

mysql> set global bulk_insert_buffer_size=32*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_buffer_pool_size=32*1024*1024; Query OK, 0 rows affected (0.09 SEC) mysql source /root/test.sqlCopy the code

Q&A

The parameters set above only take effect on the current connection and new connection. If they take effect permanently, please modify my.cnf. In actual use, these parameters are set only on the connection. These parameters are not applicable in the production environment. Therefore, restart the system after the import is successful to disable these parameters. Shell occurs when the mysql client is connected

[root@localhost ~]# mysql -u root -p 
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@localhost ~]# 
Copy the code

-h 127.0.0.1: -h 127.0.0.1 -h 127.0.0.1: -h 127.0.0.1

Mysql -u root -p -h 127.0.0.1

The Authentication plugin ‘caching_sha2_password’ cannot be loaded

The Authentication plugin ‘caching_sha2_password’ cannot be loaded ‘fails to be used by many users using Navicat Premium 12 to connect to MySQL database.

The reason:

The reason for this is that before mysql8, the encryption rule was mysql_native_password, and after mysql8, the encryption rule is caching_sha2_password

To solve the problem

  • One is to upgrade the Navicat driver
  • One is to restore the mysql user password encryption rule to mysql_native_password.

Here’s the second way, and the solution is as follows

1. Run the command prompt as an administrator

Log in to MySQL (remember to add environment variables)

  mysql -u root -p
  password:                                     # login mysql
Copy the code

2. Modify the password encryption rule and change the user password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; Update the user's passwordCopy the code

3. Refresh permissions and reset passwords

FLUSH PRIVILEGES; # refresh permissionCopy the code

The screenshot corresponding to the above two steps

The last

Alter user 'root'@'localhost' identified by '111111';Copy the code

Now open Navicat Premium 12 again to connect to the MySQL problem database and you will find that the connection is successful

MySQL > select * from foreign key constraint

Just learning MySQL, I believe you also around a lot of twists. It is common to encounter incompatibilities between reference columns and reference columns in MySQL foreign key constraints.

Program error:

ERROR 3780 (HY000): Referencing column 'xxx' and referenced column 'xxx' in foreign key constraint 'xxxxxx' are incompatible.
Copy the code

The reason:

Reference columns and reference columns in foreign key constraints have different encoding formats, resulting in incompatible errors.

The main reason for this is that when I set the foreign key, the corresponding foreign key was the primary key. I set automatic increment and unsigned in the primary key, but the field I used as the foreign key was not set. As a result, the types of our two fields were inconsistent, leading to the final conflict

Solutions:

  • Let’s keep the two parts uniform and add an unsigned attribute to the field where the foreign key is set

Fifth problem: In Python pymysql, the table name is passed as a variable into the query

When we learn to use Pymysql, we sometimes need to pass table names as variables into SQL statements. However, our fixed mindset sometimes limits us: For example, we know that we can pass a variable with a placeholder %s, followed by a variable. In Pymysql, we have the same problem, we can pass a variable into a SQL statement.

sql = 'insert into TabName + ' value (%s %s)'
cursor.execute(sql, [name,age])
Copy the code

If our TabName were also a variable we would be used to doing this

sql = 'insert into %s + ' value (%s %s)'
cursor.execute(sql, [tabname,name,age])
Copy the code

Then we get it wrong, and we know it doesn’t work, and then we look into the reason, and we find that we’re stuck in our stereotypes (I am anyway).

I also searched the Internet for a long time to find a solution, just think outside of this line

Error: SQL ='insert into %s + ' value (%s %s)SQL = 'cursor.execute(SQL, [tabname,name,age]);insert into %s(name,age)' %TabName + ' value (%s)'
cursor.execute(sql, [name])
Copy the code

That’s fine. Sometimes we can solve problems like this, but we often get stuck in our own mindset!