I. Relational databases
1. Data Modeling
There are three types of inter-entity relationships as follows:
1*) One-to-one model
One-to-one (one-to-one)
The relational model represents data and data links in two-dimensional tables and is the most widely used data model. Currently, a variety of commonly used databases, such as Microsoft SQL Server, Microsoft Access, Microsoft FoxPro, Oracle, MySQL, SQLite, etc., belong to the relational model database management system.
2*) One-to-many model
One-to-many (one-to-many)
Hierarchical models use a tree structure to represent the connections between data; the nodes of the tree are called records, and there are only simple hierarchical relationships between records. There is and only one node that has no parent node, which is called the root node; the other nodes have and have only one parent node.
3*) Many-to-many modeling
Many-to-many (many-to-many)
There can be any number of nodes without a parent. A node is allowed to have more than one parent. There can be two or more connections between two nodes.
2. Facial object model
1*) Object modeling concepts
Object-oriented model is based on object-oriented technology to develop a data model, which uses an object-oriented approach to database design.
2*) Object model characteristics
The object-oriented model of the database kind of storage object to the object as a unit, each object contains the properties and methods of the object, with class and inheritance and other characteristics.
II. Understanding the concepts and characteristics of relational databases
Concepts and characteristics of relational databases
basic concept
(i*) Relationship
The connection between data and data is called a relationship.
ii*) Two-dimensional tables
Relational databases use two-dimensional tables to represent and store relationships; a relationship is a two-dimensional table. The rows in a 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. A column in a table is a data item in a record called a field. Fields are also called attributes or columns. Each record can contain multiple fields, and different records contain the same fields (fields with different values). For example, each record in the Users table contains fields such as username, login password, etc.
Relational databases do not allow duplicate records in a table.
(VI*) Keywords
A field or combination of fields that uniquely identifies a record is called a keyword. A table can have more than one keyword, where the keyword used to identify a 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, and the primary keyword is not allowed to be repeated when adding records.
(VII*) External keywords
If a field or combination of fields in one table serves as the primary keyword for another table, such a field or combination of fields is called an external keyword.
Basic features
1. A table in a relational database is a two-dimensional table, and the fields in the table must be non-redivisible, i.e., tables in tables are not allowed.
2. 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, you can exchange the order of records.
5. The order of the fields in the record does not affect the data and the order of the fields can be exchanged.
III. Common field data types
data table
IV. Make Mysql and Pymysql link successfully
The first step is to find the datasheet login link in Navicat Premium 12.
Note: The data table must exist or the data modification will not be appended ！！！！
Step 2: Login to spyder or pycharm and use pymysql to call mysql after the link is successful.
Note: the blogger used spyder
import pymysql
# Define database link parameters
host = '127.0.0.1' # Or use local host
port = 3306
db = 'student'
user = 'root'
password = 'lyt2529165097'
conn = (host=host, port=port, db=db, user=user, password=password)
def main():
cursor = () # Streaming cursor, returns tuples by default.
return cursor
This is accomplished through the use of thespyderInside, enter the host number port number hostname (of a networked computer) You can log in with your password, etc.
Step 3: Write code to log in to the system
1. Prepare 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('Thanks for using the Student Information Management System!')
break
Of course, how can you not need a password to enter the system. Be sure to set up a password for yourself Oh Otherwise people delete your code in the middle of the night!!!!
2. Preparation of user name and password for access to the system
def login():
administartor = input('Please enter user name:')
password = input('Please enter the password:')
if administartor == 'Thundercloud Teng' and password == 'lyt2529165097':
After successfully entering the system, you can write the next program, for example, we write all the menu system, so that there is a whole structure
3. Preparation of the menu for selection after login
Login Main Menu
#LoginBehindMenu ----Expand
def login():
administartor = input('Please enter user name:')
password = input('Please enter the password:')
if administartor == 'Thundercloud Teng' and password == 'lyt2529165097':
print("Congratulations on successfully logging into the system!!!")
while True:
print('Student Information Management System')
print('================')
print('1. Adding student records')
print('2. Search for student records')
print('3. Modification of student records')
print('4. Deletion of student records')
print('5. Display all student records')
print('6. Return to the upper 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('Account or password error!')
Successfully enter the system and have a menu system when you can write the next program, for example, we first write the program to insert records
4. Prepare procedures for adding student records
# Insert student records
def add_student():
cursor = main()
id = int(input('School number:'))
name = input('Name:')
gender = input('Gender:')
age = int(input('Age:'))
class1 = input('Classes:')
major = input('Specialization:')
college = input('College:')
add = ('insert into stu (id, name, gender, age, class1, major, college)\
values(%s,%s,%s,%s,%s,%s,%s)',(id, name, gender, age, class1, major, college))
if add == 1:
()
print('Insertion successful!')
else:
print('Insertion failed!')
Step 4: Write code to query student information records
I*) Search for student records by student number
# Search by student number
def Q_by_id():
cursor = main()
choice_id = int(input('Please enter your school number:'))
('select * from stu where id =%s',(choice_id))
students = ()
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 the inquiry(yes/no)：')
if re == 'yes':
Q_by_id()
else:
query_student()
II*) Search student records by name
# Search by name (in case the student number is entered incorrectly)
def Q_by_name():
cursor = main()
choose_name = input('Please enter your name:')
('select * from stu where name =%s',(choose_name))
students = ()
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 the query yes/no:')
if re == 'yes':
Q_by_name()
else:
query_student()
III*) Search for all student information records
# Query all students
def Q_all():
cursor = main()
('select * from stu')
students = ()
for student in students:
print('\t{}\t{}\t{}\t{}\t{}\t{}\t{}' .format(student[0], student[1], student[2], student[3], student[4], student[5], student[6]))
The query is successful! If you still want to query, you can write code to realize it!
VI*) Write "whether" to search for records
re = input('Whether to continue the inquiry(yes/no)：')
if re == 'yes':
Q_by_id()
else:
query_student()
Of course, in addition to being able to query, there must be a menu that we can choose! Otherwise we can't access the program to add, delete, change or check.
VII*) After writing the login menu we also have to write the menu that is available for our queries
Query Record Menu
# Query menu
def query_student():
while True:
print('Search for student records')
print('================')
print('1. Search student records by student number')
print('2. Search student records by name')
print('3. Query all student records')
print('4. Return to the upper 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: It's not just about writing the code, it's about writing the menu options.
VII*) Programming of deletion of student records
Menu for deleting records
# Deleted menus
def delete1_student():
print('============================')
print('1. Delete all information of students')
print('2. Go back to the initial interface')
print('============================')
mc4 = int(input('Input menu number:'))
if mc4 == 1:
delete_student()
elif mc4 == 3:
login()
Delete student's name as Lei Yunteng
First you need to know his school number, then his name.
And then comes the hardest part of our program, and our main event, the revision of the assigned student records.
Still need to know his school number, his name
def update_student():
cursor = main()
cur= int(input('Please enter the student number of the student you wish to modify:'))
('select * from stu where id = %s', (cur))
if () == []:
print('School number not found is{}schoolchildren'.format(cur))
mc3 = input('Whether to re-query？(yes/no)')
if mc3 != 'no':
update_student()
else:
login()
else:
print('==============')
print('1. Change of name')
print('2. Modification of gender')
print('3. Revision of age')
print('4. Modify the class')
print('5. Modification of specialization')
print('6. Modification of colleges')
print('7. Return to the upper menu')
print('==============')
mc2 = int(input('Please enter the menu number:'))
if mc2 == 1:
name = input('Please enter a modified name:')
a = ('update stu set name = %s where id = %s', (name, cur))
if a == 1:
()
print('Modification successful!')
else:
print('Modification failed!')
elif mc2 == 2:
gender1 = input('Please enter the modified gender:')
a = ('update stu set genden = %s where id = %s', (gender1, cur))
if a > 1:
()
print('Modification successful!')
else:
print('Modification failed!')
elif mc2 == 3:
age1 = int(input('Please enter the revised age:'))
a = ('update stu set age = %s where id = %s', (age1, cur))
if a > 1:
()
print('Modification successful!')
else:
print('Modification failed!')
elif mc2 == 4:
class1 = input('Please enter the modified class:')
a = ('update stu set class = %s where id = %s', (class1, cur))
if a > 1:
()
print('Modification successful!')
else:
print('Modification failed!')
elif mc2 == 5:
major1 = input('Please enter the modified specialization:')
a = ('update stu set major = %s where id = %s', (major1, cur))
if a > 1:
()
print('Modification successful!')
else:
print('Modification failed!')
elif mc2 == 6:
college1 = input('Please enter the modified college:')
a = ('update stu set college = %s where id = %s', (college1, cur))
if a > 1:
()
print('Modification successful!')
else:
print('Modification failed!')
else:
pass# Occupy a space character
When I haven't closed the table and it hasn't been updated yet, we need to close the updated table and reopen it.
At this point we close the table and he will update the data we have changed
Of course, in addition to changing your name, you can also change your student number, gender, college, and so on...
V. All source code (specify)
This will not tell you to tell you so much knowledge, I believe that more look at more practical training must be able to play or even do better.
VI. Blog Summary
1. Why do you write code
Now the code will not only increase their knowledge and thinking with the increase in knowledge will also affect other things, of course, in the blog often encounter some life, learning on the seemingly intractable problems, then I am always struggling to force myself to think that I can find a way out. Over time, I have developed a mindset that things can always be answered. Every time I write a blog or an article that gets praised by my peers and teachers, I get excited and it paves the way for a better blog for the next time.
2. Specific thinking
In the process, I again felt that writing code was also similar to the pattern of problem solving I had done before, and that each time I completed something complete, I formed a small part of a specific pattern of thinking, and maybe that's what makes writing code so much fun.
3. Inspired thinking
When I am confused every time a problem for a long time can not be solved when I will feel very depressed, this time I will try to go out for a breath of fresh air, stretching a lazy body to empty your mind. You will find that your body is suddenly much more relaxed. When you come back to solve the problem again, you find it is very easy. Sometimes inspiration comes when you've been depressed for a long time and then suddenly relax, that moment.
4. Brain + Hand
Cultivate your own programming thinking, cultivate your own programming fun. Code is not out of thin air, is a line by line knocked out, running success there is no shortcut only by their own brain + hand.
The above is Python through pymysql call MySQL to add, delete, change and remove the details of the check, more information about Python pymysql add, delete, change and remove the check, please pay attention to my other related articles!