python: introduction to database connection operation

Original location:
python: introduction to database connection operation - JYRoy - blog Garden https://www.cnblogs.com/jyroy/p/9429897.html
Module#

1 import pymssql,pyodbc

Module description#
pymssql and pyodbc modules are commonly used to connect and operate databases such as SQL Server and MySQL. Of course, some other modules can also perform corresponding operations, such as adodbapi, mssql, mxODBC, etc. we can choose one of them in actual use. Each module has a corresponding supporting version and platform, You can consult the documents by yourself https://wiki.python.org/moin/SQL%20Server

Module installation#

1 pip install pymssql

I mentioned the use of pip in another blog post https://www.cnblogs.com/jyroy/p/9410593.html

Module use#
If we use python to operate the database, the first step should be to connect the database. Here, we use the connect method in pymssql module and the connect method in pyodbc module.

Create a connection object using connect
connect.cursor creates cursor objects, and SQL statements are basically executed on cursors
cursor.executeXXX method executes SQL statement, cursor Fetchxxx obtains query results, etc
Call the close method to close the cursor and database connection

pymssql module connection

Copy code
'''
pymssql module connects to SQL Server database
'''

import pymssql   

 '''Format 1'''
host= "XXXXXXXXXXXX"  # Database server name or IP
user = "test"
password = "123"
database = "test"

conn = pymssql.connect(host, user, password, database)

'''Format 2'''
conn = pymssql.connect(host='XXXXXXXXXXXX', user="test", password="123", database="test")

Copy code

pyodbc module connection

 import pyodbc

    conn = pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=test;DATABASE=test;UID=user;PWD=password')

Different SQL server versions have different DRIVER fields. The corresponding relationship is as follows:

{SQL Server} - released with SQL Server 2000
{SQL Native Client} - released with SQL Server 2005 (also known as version 9.0)
{SQL Server Native Client 10.0} - released with SQL Server 2008
{SQL Server Native Client 11.0} - released with SQL Server 2012
To use pyodbc, you need to install Microsoft's official native client (if there is no installation, the error im002 will be reported). The installation of SQL server management studio will be automatically accompanied by the installation (the installed version can be seen in the control panel). If not installed, you need to https://msdn.microsoft.com/en-us/data/ff658533.aspx Download and install (sqlncli.msi). It is recommended to select the native client corresponding to the remote database version. If the locally installed native client is a higher version, the DRIVER={SQL Server Native Client 11.0} needs to fill in the local higher version.

Get database content#

This involves the use of cursors (if you don't understand cursors, please refer to the boss's blog https://www.cnblogs.com/selene/p/4480328.html )

1     '''
2     Use of cursors
3     '''
4     cursor_1 = conn.cursor()     #Get cursor
5 
6     cursor_1.execute("select Sno from student")    #Execute statement
7 
8     print (cursor_1.fetchone())              #The result is a tuple, and fetchone() gets the query result
9  

fetchone(): returns a single tuple, that is, a row. If there is no result, it returns None

fetchall(): returns multiple tuples, that is, multiple rows. If there is no result, it returns ()

 1     '''
 2     fetchall()Use of
 3     '''
 4     cursor_2 = conn.cursor()
 5 
 6     cursor_2.execute("select Sno, Sname from students")
 7 
 8     rows = cursor_2.fetchall()
 9 
10     for row in rows:
11         print(row.Sno, row.Sname)

Since execute returns the cursor itself, if you need to get all the contents directly at once, you can use the cursor itself directly

1 cursor.execute("select Sno, Sname from students"):
2
3 for row in cursor:
4 print(row.Sno, row.Sname )

There is a special explanation for cursors,

A connection can only have one cursor query active at a time. You can see the following code for the specific meaning.

1     cursor_1 = conn.cursor()     #Get cursor
2     cursor_1.execute("select Sno from student")    #Execute statement
3 
4     cursor_2 = conn.cursor()
5     cursor_2.execute("select * from student where Sno=1")
6 
7     print (cursor_1.fetchall())             #Cursor is displayed_ Query results of 2
8     print (cursor_2.fetchall())             #No results are displayed

Provide a solution

1      '''Solve the above problems'''
 2 
 3     cursor_1.execute("select Sno from student")
 4     cursor_list_1 = cursor_1.fetchall()   #Use the list to store the results
 5 
 6     cursor_2.execute("select * from student where Sno=1")
 7     cursor_list_2 = cursor_2.fetchall()
 8 
 9     print (cursor_list_1)               #Get cursor through print list_ Results of 1
10     print (cursor_list_2)    

In the normal use of cursors, the query results obtained by cursors are one tuple per row.

In actual use, we can use as according to the needs_ The dict method returns a dictionary variable, where the Key of the dictionary is the column name of the data table

 1     '''Cursor return behavior dictionary variable'''
 2 
 3     cursor_3 = conn.cursor(as_dict=True)      #Specify as when creating a cursor_ The dict parameter causes the cursor to return dictionary variables
 4     cursor_3.execute("select * from student")
 5 
 6     for row in cursor_3:
 7         print('ID=%d' % row['Sno'])    #The key name is the column name of the list {Sno:ID}
 8     
 9     print (cursor_3.fetchone())
10     
11     conn.close()     #Call the close method to close the cursor and database connection

If you think the above code looks too long, please provide you with a database operation solution with a small amount of code

The with statement, that is, the context manager, has the advantages of high code integration and omitting the need to call the close method to close the connection and cursor

1     '''
 2     use with Statement (context manager)
 3     '''
 4 
 5     '''through the use of with Statement to omit the displayed call close Method to close the connection and cursor'''
 6     with pymssql.connect(host='LAPTOP-3OTJHAG9', user="sa", password="123", database="TEST") as conn:    
 7 
 8     #Equivalent to conn = pymssql connect(host='XXXXXXXXXXXX', user="test", password="123", database="test")
 9 
10         with conn.cursor(as_dict=True) as cursor_4:
11             cursor_4.execute('select * from student')
12             for row in cursor_4:
13                 print ('ID=%d' %(row['Sno']))

On the addition, deletion and modification of database#

Adding, deleting and modifying the contents of the database is also to directly pass the SQL statement to the execute method. However, it should be noted that after running, you need to submit changes with commit

1 cursor.execute("insert into students(id, name) values ('123', 'My name')")
2 conn.commit()    

3 cursor.execute("delete from studnts where id='123'")

4 conn.commit()
 

About pymssql module, there are still things you don't understand. You can see the official website http://www.pymssql.org/

Author: JYRoy

source: https://www.cnblogs.com/jyroy/p/9429897.html

Please indicate the author and source in the obvious position of the article.

Classification: Python, database
Tags: python, database, SQL Server, pymssql