Shulou(Shulou.com)06/02 Report--

This article mainly introduces how Python calls MySQL through pymysql to add, delete, modify, remove and check, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

1. Relational database 1. Data model

There are three types of relationships between entities:

1 *) one-to-one model

One to one (one-to-one)

The relational model uses two-dimensional tables to represent data and data connections, which is the most widely used data model. At present, all kinds of commonly used databases, such as Microsoft SQL Server, Microsoft Access, Microsoft FoxPro, Oracle, MySQL, SQLite and so on, belong to the relational model database management system.

2 *) one-to-many model

One to many (one-to-many)

The hierarchical model uses a tree structure to represent the relationship between data. the nodes of the tree are called records, and there is only a simple hierarchical relationship between records. One and only one node does not have a parent node, which is called the root node; the other nodes have one and only one parent node.

3 *) many-to-many model

Many to many (many-to-many)

You can have any number of nodes without a parent. A node allows multiple parent nodes. There can be two or more connections between two nodes.

two。 Face object model

1 *) object model concept

Object-oriented model is a kind of data model developed on the basis of object-oriented technology. It uses object-oriented method to design database.

2 *) characteristics of object model

The database of the object-oriented model stores objects in units of objects, each object contains the properties and methods of the object, and has the characteristics of class and inheritance.

Second, understand the concept and characteristics of relational database

The concept and characteristics of Relational Database

Basic concept

I *) relationship

The relationship between data and data is called a relationship.

Ii*) 2D table

Relational databases use two-dimensional tables to represent and store relationships, and a relationship is a two-dimensional table. The rows in the table are called records and the columns are called fields. A database can contain multiple tables.

Iii*) records and fields

A row in a table is called a record. The data items in the columns in the table are called fields. A field is also called a property or column. Each record can contain multiple fields, and different records contain the same field (with different values for the field). For example, each record in the user table contains fields such as user name, login password, and so on.

A relational database does not allow duplicate records in a table.

VI*) keyword

Fields or combinations of fields that can uniquely identify a record are called keywords. A table can have multiple keywords, in which the keyword used to identify the record is called the primary keyword, and the other keywords can be called candidate keywords. Only one primary keyword is allowed for a table. For example, the user name in the user table can be defined as the primary keyword, which is not allowed to be repeated when adding records.

VII*) external keyword

If a field or combination of fields in one table is the primary key of another table, such a field or combination of fields is called an external key.

Basic characteristics

1. The table in the relational database is a two-dimensional table, and the fields in the table must be inseparable, that is, tables in the table are not allowed.

two。 Duplicate records are not allowed in the same table.

3. Duplicate fields are not allowed in the same record.

4. The order of records in the table does not affect the nature of the data, and the order of records can be exchanged.

5. The order of the fields in the record does not affect the data, and the order of the fields can be exchanged.

Third, commonly used field data types

Data table

Fourth, make the Mysql and Pymysql link successful

The first step is to find the data table login link in Navicat Premium 12

Note: the data table must exist, otherwise the data modification can not be added!

The second step is to log in to spyder or pycharm and use pymysql to call mysql after displaying the successful link.

Note: the spyder used by the blogger

Import pymysql# defines the database link parameter host= '127.0.0.1' # or use local hostport = 3306db = 'student'user =' root'password = 'lyt2529165097'conn = pymysql.connect (host=host, port=port, db=db, user=user, password=password) def main (): cursor = conn.cursor (pymysql.cursors.SSCursor) # streaming cursor. By default, the returned tuple return cursor can log in by entering the host number, port number, host name, password, etc.

The third step, write code to log in to the system

1. Write the system login and exit interface

While True: print ('Please select the following menu number:') print ('='* 3) print ('1, log in to the student information management system') print ('2, Exit the student information management system') print ('='* 3) mc1 = int (input ('enter menu number:') if mc1 = = 1: login () elif mc1 = = 2: print ('Thank you for using the student information management system!') Break

Of course, it's impossible to get into the system without a password. Be sure to set your own password, or others delete your code in the middle of the night!

two。 Write the user name and password to enter the system

Def login (): administartor = input ('Please enter user name:') password = input ('Please enter password:') if administartor = = 'Lei Yunteng' and password = = 'lyt2529165097':

After successfully entering the system, we can write the next program, for example, we write all the menu systems, so that we have an overall architecture.

3. Write menus to choose from after login

Log in to the main menu

# menu after login-expand def login (): administartor = input ('Please enter user name:') password = input ('Please enter password:') if administartor = = 'Lei Yunteng' and password = = 'lyt2529165097': print ("Congratulations on your successful login to the system!") While True: print ('student information management system') print ('=') print ('1, add student records') print ('2, query student records') print ('3, modify student records') print ('4, delete student records') print ('5, Show all student records') print ('6, Return to the superior menu') print ('=') mc2 = int (input ('enter menu number:') if mc2 = = 1: add_student () elif mc2 = = 2: query_student () elif mc2 = = 3: update_student elif mc2 = = 4: delete_student () elif mc2==5: print_student () else: break else: print ('wrong account or password')

When you successfully enter the system and have a menu system, you can write the next program, for example, we can write a program to insert records first.

4. Write a program to increase student records

# insert student record def add_student (): cursor = main () id = int (input ('student number:') name = input ('name:') gender = input ('gender:') age = int (input ('age:') class1 = input ('class:') major = input ('major:') college = input ('college:') add = cursor.execute ('insert into stu (id, name, gender, age) Class1, major, college)\ values (% sdepartment% sjue% s)', (id, name, gender, age, class1, major, college) if add = = 1: conn.commit () print ('inserted successfully!') Else: print ('insert failed!')

The fourth step is to write the code to query the records of student information.

I *) query the student records according to the student number

# query def Q_by_id () by student number: cursor = main () choice_id = int (input ('Please enter student ID:') cursor.execute ('select * from stu where id =% slots, (choice_id)) students = cursor.fetchall () for stu in students: print (stu [0], stu [1], stu [2], stu [3], stu [4], stu [5] Stu [6]) print ('query successful') re = input ('whether to continue with the query (yes/no):') if re = = 'yes': Q_by_id () else: query_student ()

II*) query student records by name

# query by name (in case the student ID is entered incorrectly) def Q_by_name (): cursor = main () choose_name = input ('Please enter your name:') cursor.execute ('select * from stu where name =% name, (choose_name)) students = cursor.fetchall () for stu in students: print (stu [0], stu [1], stu [2], stu [3], stu [4], stu [5] Stu [6]) print () re = input ('whether to continue querying yes/no:') if re = =' yes': Q_by_name () else: query_student ()

III*) query the information records of all students

# query all students def Q_all (): cursor = main () cursor.execute ('select * from stu') students = cursor.fetchall () for student in students: print ('\ t {}\ t (student [0], student [1], student [2], student [3], student [4], student [5], student [6]))

In this way, the query is successful! If you still want to make a query, you can also write code to achieve it

VI*) write "whether" to query the record

Re = input ('whether to continue with the query (yes/no):') if re = = 'yes': Q_by_id () else: query_student ()

Of course, in addition to the query, of course, there must be a menu for us to choose from! Otherwise, we will not be able to enter the program to add, delete, modify and check.

VII*) after writing the login menu, we should also write the menu that we can query

Query record menu

# query menu def query_student (): while True: print ('query student records') print ('=') print ('1, query student records by student number') print ('2, query student records by name') print ('3, query all student records') print ('4, Return to the superior menu') print ('=') mc3 = int (input ('Please enter the menu number of the query:') if mc3 = = 1: Q_by_id () elif mc3 = = 2: Q_by_name () elif mc3 = = 3: Q_all () else: break

Note: not only write the code, but also write the menu to choose from.

VII*) write a program to delete student records

Delete record menu

# deleted menu def delete1_student (): print ('=') print ('1, delete all student information') print ('2, return to the original interface') print ('= =') mc4 = int (input ('Input menu number:')) if mc4 = = 1: delete_student () elif mc4 = = 3: login ()

Delete the information whose student's name is Lei Yunteng.

First of all, we need to know his student number, and then his name.

And then there's our hardest part, and it's also our highlight of revising the designated student records.

We still need to know his student number and name.

Def update_student (): cursor = main () cur= int (input ('Please enter the student number you want to modify:') cursor.execute ('select * from stu where id =% student, (cur)) if cursor.fetchall () = []: print (' No student whose student number is {} '.format (cur)) mc3 = input (' do you want to re-query? (yes/no)') if mc3! = 'no': update_student () else: login () else: print (' =') print ('1, change name') print ('2, change gender') print ('3, Modify age') print ('4, modify class') print ('5, modify major') print ('6, modify college') print ('7, Return to the superior menu') print ('=') mc2 = int (input ('Please enter the menu number:') if mc2 = = 1: name = input ('Please enter the modified name:') a = cursor.execute ('update stu set name =% s where id =% s') (name, cur) if a = = 1: conn.commit () print ('modified successfully!') Else: print ('failed to modify!') Elif mc2 = = 2: gender1 = input ('Please enter the modified gender:') a = cursor.execute ('update stu set genden =% s where id =% cur, (gender1, cur)) if a > 1: conn.commit () print (' modified successfully!') Else: print ('failed to modify!') Elif mc2 = 3: age1 = int (input ('please enter the modified age:') a = cursor.execute ('update stu set age =% s where id =% cur, (age1, cur)) if a > 1: conn.commit () print (' modified successfully!') Else: print ('failed to modify!') Elif mc2 = = 4: class1 = input ('Please enter the revised class:') a = cursor.execute ('update stu set class =% s where id =% cur, (class1, cur)) if a > 1: conn.commit () print (' modified successfully!') Else: print ('failed to modify!') Elif mc2 = = 5: major1 = input ('Please enter the modified major:') a = cursor.execute ('update stu set major =% s where id =% slots, (major1, cur)) if a > 1: conn.commit () print (' modified successfully!') Else: print ('failed to modify!') Elif mc2 = = 6: college1 = input ('Please enter the modified college:') a = cursor.execute ('update stu set college =% s where id =% cur, (college1, cur)) if a > 1: conn.commit () print (' modified successfully!') Else: print ('failed to modify!') Else: pass# occupies a space character

It hasn't been updated when I haven't closed the data table, so we need to close the updated data table and reopen it.

At this time, we close the data table and he will update the data we have changed.

Of course, in addition to changing the name, you can also change the student number, gender college and so on.

Thank you for reading this article carefully. I hope the article "how Python calls MySQL through pymysql to add, delete, modify, remove and search" will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.