Python Connection Operations Database

One, the following is what we are talking about is the application connected to MySQL:

1, in fact, in Python connection operation MySQL module has a number, here I only show you pymysql this one module (actually I feel it is more useful only):

Pymysql is a third-party module, we need to install, here by the way to everyone to install the third-party module knowledge; First, if we install on Windows, we need to install the Python interpreter, and then bring

The PIP module (we install the third-party module, we need this command to install), after configuring the environment variables we can directly in the DOS command line to execute the module to be installed, as follows:

I have installed here, so prompt me to update, after you install the Pymysql module, you can go directly to the call, below we look at a small example, according to this small example to illustrate:

1 ImportPymysql2 3 #link the database and create cursors4conn = Pymysql.connect (host='192.168.163.131', port=3306,user='mysql_test', passwd='123456', db='Pymysql')5Cur =conn.cursor ()6 7 #cur.execute (' CREATE TABLE T1 (ID INT (Ten), Name VARCHAR ()) Engine=innodb DEFAULT Charset=utf8 ')8 9 #Cur.execute (' INSERT into T1 (id,name) VALUES (1001, ' Madaqiang ') ')TenCount = Cur.executemany ('INSERT INTO T1 (id,name) VALUES (%s,%s)', [(1002,'Eric'), (1003,'Tom')]) One  A Conn.commit () -  - cur.close () theConn.close ()

Explanatory notes:

First use the Pymysql Connect method to the database, the parameters inside I believe that everyone can understand, the cur is created by a cursor (Execute SQL, all use this cursor to execute); Execute SQL statement

The two lines of the note, respectively, are to create a T1 table, and then insert a piece of data, the following executemany can be inserted in a number of records at one time, each record is a tuple, count is the number of rows affected; and finally you need to do

Commit () method, otherwise, you do this in vain, and then close both the cursor and the connection.

Simple connections are just such a simple thing. Now let's look at the cursor problem, I believe a lot of people at the beginning of the same time as me, the cursor understanding is not so accurate, the following two pieces of code, you understand:

1conn = Pymysql.connect (host='192.168.163.131', port=3306,user='mysql_test', passwd='123456', db='Pymysql')2Cur =conn.cursor ()3Cur.execute ('SELECT * from T1')4 5 #cur.scroll (1,mode= ' relative ')6 7 #Take out the first piece of data8line =Cur.fetchone ()9 Print(line)Ten #Take out all the data OneAll_line =Cur.fetchall () A Print(All_line) - Conn.commit () -  the cur.close () -Conn.close ()

First we look at this code, the new two has been labeled in the code, is to take out the first data and all the data, but we look at the output below, you will find some problems:

1 ' Madaqiang ' )2'Eric'Tom')

After you output the first data, the cursor swims to the second data, you output all the data, in fact, from the second line to start the output; If you do not want to output 1002 This record, you can add a statement below the Nineth line:

Cur.scroll (1,mode= ' relative '), so will not output, if you feel the output of the Ganso, later call will be more troublesome, we can set the output format as a dictionary format, so whether it is to see or call is more convenient:

In fact, we just need to add cur = conn.cursor (cursor=pymysql.cursors.dictcursor) underneath the cursor, so that a line of code is OK, or output the results, otherwise it will always feel less what:

1 #link the database and create cursors2conn = Pymysql.connect (host='192.168.163.131', port=3306,user='mysql_test', passwd='123456', db='Pymysql')3Cur =conn.cursor ()4 #convert the output format to5cur = conn.cursor (cursor=pymysql.cursors.DictCursor)6 7Cur.execute ('SELECT * from T1')8 9 Ten #Take out the first piece of data Oneline =Cur.fetchone () A Print(line) -  - Conn.commit () the  - cur.close () -Conn.close (
===================== Output Results =======================
{' name ': ' Madaqiang ', ' ID ': 1001}

2. Let's take a look at an ORM framework (SQLAlchemy),

SQLAlchemy is one of the most widely used ORM tools in the Python world, using a data mapping model like Hibernate in Java, rather than the active record model used by other ORM frameworks, SQLAlchemy is divided into two parts, One is the common ORM object Mapping and the other is the core SQL expression. The first one is very well understood, the pure ORM, the latter is not ORM, but the DBAPI package, with some SQL expressions to avoid direct write SQL statements. Simply summarize: Convert the object to SQL and then execute the SQL using the data API and get the execution results.

Let's take a look at the implementation mechanism of the SQLAlchemy ORM, what is encapsulated inside:

The dialect is used to communicate with the data API and to invoke different database APIs depending on the configuration file, enabling operations on the database, such as:

mysql+pymysql://<username>:<password>@/<dbname>[?<options>]

Python Connection Operations Database