Django ORM------Mysql

ORM Operation

SELECT * from TB where ID > 1

#对应关系

Models.tb.objects.filter (Id__gt=1)

Models.tb.objects.filter (id=1)

Models.tb.objects.filter (Id__lt=1)

Create Class

1.db-first: Automatically create database tables based on class

#models. py

settings.py to register the app

The default generated table name is: #app01_userinfo

 from Import Models # Create your models here. class UserInfo (models. Model):    #Django will create an ID column by default, self-increment, primary key    # user name, string type, specified length    Username = models. Charfield (max_length=32)    = models. Charfield (max_length=64) project Settings->installed_apps=# based on the generated py file, Generate Database directly

View Code

Set the database used in Project settings.py->database={}: Default Connection SQLite

To connect the MySQL configuration file:

DATABASES = {    'default': {    'ENGINE':'Django.db.backends.mysql',    'NAME':'dbname',    'USER':'Root',    'PASSWORD':'XXX',    'HOST':"',    'PORT':"',    }}

View Code

Note ***********

Django uses the MySQLdb module to connect to MySQL by default, so it will error and should be modified to Pymysql connection

In the project's __init__.py file, add: Import Pymysqlpymysql.install_as_mysqldb ()

2.code-first: Perform various operations on data in a database table based on a class

I. Overview

1. What is a database?

　A: Data warehouses, such as: In the ATM example we created a DB directory called the database

2. What is MySQL, Oracle, SQLite, Access, MS SQL Server, etc.?
　A: They are all software and have two main functions:

• A. Saving data to a file or memory
• B. Receive a specific command, and then perform the appropriate action on the file
  
  PS: If you have the above software, do not have to create files and folders yourself, but instead of directly passing commands to the above software, let them for file operations, they collectively referred to as the database management system (Dbms,database Management Systems)

3. What is SQL?
A: The above mentioned MySQL and other software can accept the command, and do the appropriate action, because the command can contain delete files, get the contents of the file, and so many operations, for the written command is the SQL statement. SQL????????, is???????????????????????????? Structured language (structured Query Language???????? ) of the abbreviation, SQL???????????????????????????????? is a language specifically designed to communicate with a database.

Second, download the installation

MySQL is a relational database management system developed by the Swedish MySQL AB company, currently owned by the Oracle company. MySQL's most popular relational database management system, MySQL is one of the best RDBMS (relational database Management system, relational databases management systems) application software in WEB applications.

To use MySQL to store and manipulate data, you need to do a few things:
A. Installing the MySQL server
B. Installing the MySQL Client
B. "Client" Connection "server Side"
C. "Client" sends a command to the "server-side MySQL" Service to accept the command and perform the appropriate operation (increase and deletion of the search, etc.)

Download        http://dev.mysql.com/downloads/mysql/Install        windows:            dot dot        Linux:            yum install Mysql-server        MAC:            Dot Dot

Window version

1. Download

5.7.  - http: // dev.mysql.com/downloads/mysql/

View Code

2. Decompression

If you want MySQL to be installed in the specified directory, then move the extracted folder to the specified directory, such as: C:\mysql-5.7.16-winx64

3. Initialization

After MySQL extracted the bin directory there is a lot of executable files, execute the following command to initialize the data:

CD c:\mysql-5.7. ---initialize-insecure

View Code

4. Start the MySQL service

Execute the command to start the MySQL service

# Enter the executable directory CD C:\mysql-5.7. -winx64\bin # start MySQL service mysqld

View Code

5. Start the MySQL client and connect to the MySQL service

The default password is not set to the root account due to the "mysqld--initialize-insecure" command used during initialization

# Enter the executable directory CD C:\mysql-5.7.16-winx64\bin # connection MySQL server mysql-u root-p # Prompt Please enter the password, go directly

Enter the entry to see that the installation was successful:

To this end, the MySQL server has been successfully installed and the client is ready to connect, and in the future to operate MySQL, it is only necessary to repeat the 4, 5 steps. However, in the 4, 5 steps of repeated access to the executable directory is cumbersome, if you want to be easy to operate later, you can do the following operations.

A. Adding environment variables

Add the MySQL executable to the environment variable to execute the command

"Right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the second content box to find the variable named path of a row, double-click"-"to append the MySQL bin directory path to the variable value, and split" such as: \ C Program Files (x86) \parallels\parallels tools\applications;%systemroot%\system32;%systemroot%;%systemroot%\ System32\wbem;%systemroot%\system32\windowspowershell\v1.0\; C:\Python27; C:\Python35; C:\mysql-5.7.16-winx64\bin

This way, when you start the service and connect later, you only need to:

# to start the MySQL service, enter Mysqld # on the terminal to connect the MySQL service in terminal input: Mysql-u root-p

B. Making a MySQL service into a Windows service

The previous step solves some problems, but is not exhaustive, because the current terminal will be stuck while executing "MYSQD" to start the MySQL server, then do the setup to resolve the problem:

# make MySQL Windows service, execute this command at Terminal: "C:\mysql-5.7.16-winx64\bin\mysqld"--install # Remove the MySQL Windows service and execute this command at the terminal: "C \ Mysql-5.7.16-winx64\bin\mysqld "--remove

After registering as a service, you only need to execute the following command when you start and close the MySQL service later:

# Start MySQL service net start MySQL # close MySQL service net stop MySQL

Linux version

Installation:

1	yum install mysql-server

Service-side startup

1	mysql.server start

Client connections

1234567

连接：    mysql -h host -u user-p    常见错误：        ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘ (2), it means that the MySQL server daemon (Unix) or service (Windows) isnotrunning.退出：    QUIT 或者 Control+D

Third, database operation

1. Display Database

1	SHOW DATABASES;

Default database:
MySQL-User rights-related data
Test-for user testing data
Information_schema-mysql itself schema-related data

2. Create a database

12345

# utf-8CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;# gbkCREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

3. Using the Database

1	USE db_name;

Displays all tables in the currently used database: show TABLES;

4. User Management

12345678910

创建用户    create user ‘用户名‘@‘IP地址‘identified by ‘密码‘;删除用户    drop user ‘用户名‘@‘IP地址‘;修改用户    rename user ‘用户名‘@‘IP地址‘; to ‘新用户名‘@‘IP地址‘;;修改密码    set password for‘用户名‘@‘IP地址‘ = Password(‘新密码‘) PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

5. Authorization Management

123	show grants for‘用户‘@‘IP地址‘      -- 查看权限grant  权限 on 数据库.表 to   ‘用户‘@‘IP地址‘ -- 授权revoke 权限 on 数据库.表 from ‘用户‘@‘IP地址‘-- 取消权限

　　

　　

Django ORM------Mysql