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

This article introduces the relevant knowledge of "how to learn Python database with zero foundation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Classification of databases

Relational database and non-relational database

1)。 What is a relational database?

Relational database refers to the database that uses the relational model to organize the data. to put it simply, the relational model refers to the two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and their relationships. For example, the banking system uses a lot of relational databases. For example, MySQL, which we often use, is a typical relational database.

Advantages:

Easy to understand: two-dimensional table structure is a concept that is very close to the logical world, and the relational model is easier to understand than other models such as mesh, hierarchy and so on.

Easy to use: the general SQL language makes it very convenient to operate relational databases.

Easy to maintain: rich integrity (physical, referential, and user-defined integrity) greatly reduces the probability of data redundancy and data inconsistencies

2)。 What is a non-relational database

Although the relational database is very good, with the large-scale outbreak of the Internet, the weakness is becoming more and more obvious, such as transaction consistency, multi-table check, high concurrency and other bottlenecks.

So the word NoSQL comes out of the sky, stored in key-value pairs, and the structure is not fixed, each tuple can have different fields, and each tuple can add some of its own key-value pairs as needed, so it will not be limited to a fixed structure, and can reduce some time and space overhead. For example, MongoDb is a typical NoSQL database (key values for what people think of, for json format).

Writing here reminds me of the battle between IP and ATM technology at that time. In fact, no technology can dominate for several years. It is only temporary to take the lead. It is the way to conform to the trend. There is no technology, only the best fit for the times.

How does 2.Python operate the database

Now the mainstream databases on the market are Oracle,MySQL,Mongodb,Redis,SQLite,SQL Server, etc., some students will ask so many databases, how to transplant my code, rest assured that Python has a standard database interface for DB-API,Python DB-API to provide developers with a database application programming interface, that is to say, after using it to connect to each database, you can operate each database in the same way.

The process of using Python DB-API is also very simple:

a)。 Introduction of API module

b)。 Get a connection to the database

c)。 Execute related statements for query, search, and stored procedures

d)。 Close the database connection

At this point, I suddenly remembered a story (putting the elephant in the refrigerator). It's done in three steps, .

3. The construction of database environment, SQLite starts

Understand the above truth, partners will certainly be eager to practice, will soon start the journey of the database, we still choose to start from a simple start, first from SQLite to learn to use, why choose SQLite, database learning seems complex, in fact, is a layer of paper, over this obstacle, and then learn other databases are very easy.

SQLite is a very simple, free, fast and powerful small database (although the sparrow has five internal organs), does not need complex installation and management, easy to use, very suitable for rookies. And it's cool to have its own SQLite3 on both linux and Mac.

(the students of win certainly don't like it. In fact, it's very easy to install under win. You can have a look at Baidu and finish it in 3 minutes at most.)

Let's take a look at what SQLite3 looks like. Open the Mac terminal and tap sqlite3:

4. Database visualization tool

Sometimes we need to use visual tools to view our database, especially when the data table is very large, there are many tools to view SQLite database files. I recommend using DB Browser for SQLite here, which can be very intuitive to deal with SQLite files.

You can go to sqlitebrowser's official website to download, there is win,linux,mac version, mac version about 13m, you can choose to download.

5. Create a database

We first create a database, there are many ways to create a database, we can use the sqlite3 command line to create, can also use Python code to write a new database, can also use DB Browser for SQlite graphics visualization to create, for zero-based rookies, we first use the simplest graphical interface to create it.

1)。 Open DB Browser

Step1: click New Database

Step2: take a name, such as Students

Step3: click save

2)。 Build a table

A database can use many tables. Let's create a new table called demo, and the columns in it are

Id:INTEGER (equivalent to int)

Name:TEXT (equivalent to string)

Sex:TEXT (equivalent to string)

Age:INTEGER (equivalent to int)

About the grammar in SQLite, the next article will explain in detail, here we have a general understanding of it.

3)。 Edit the contents of the table

Step1: click Brower data

Step2: click New record

Step3: add the content of each line

Then save and exit, and there will be a Students.db in the place where you saved it (Desktop) above.

6. Command line query database content

We just created a new Students.db file, which is actually a 3-4 two-dimensional table. Do you want to take a look at it in sqlite? OK, let's open a terminal and open it with sqlite3 Students.db.

Knock on. Tables

A demo table appears

Knock on select * from demo

The contents of the demo table will appear.

Note:

The sql statement must end with;

Is it exactly the same as the content of the table we created with the graphical interface? With the same knowledge, the data format in SQLite is a two-dimensional table, isn't our NumPy,Pandas specializing in repairing two-dimensional tables, such as CSV,Excel files? wow, this classmate, you are so talented. Yes, a very important process of data analysis is to clean the data into a table structure and store it in the database. In fact, CSV files can be changed directly into SQL db files.

7.SQLite-Python | the protagonist enters the stage

After laying the groundwork for the first six themes, after throwing a brick to attract jade for such a long time, it is time for our Python protagonist to play. Our most important goal is to use Python to read and write the database. Today let's read it first.

1). Pip install SQLite3 module

There are many ways to install it, simply install it in pip install sqlite3 or pycharm

2)。 Read database demo tables

A) .sqlite3.connect opens the database file

b)。 Get cursor cursor

c)。 Execute a SQL statement, in this case, to query the whole table

d)。 Print the data for each line

e)。 Don't forget to close cursors and databases

This is the end of the content of "Zero Foundation how to learn Python Database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.