Python Operations SQL Server

First, the use of modules introduction and installation

1. The module used by Python to operate SQL Server is pymssql.

2, pymssql installation, directly using PIP install pymssql to install.

Ii. introduction of Python Operations SQL Server

1, database connection class and parameter introduction

The connection class for the Pymssql.connect:sqlserver connection.

Host (STR): The database host and instance that need to be connected. such as: IP, ip\sqlexpress,. \SQLExpress and so on.

User (str): The username of the connection database. such as: SA, test, and so on.

Password (str): The password of the connection database corresponding to the user name.

Database (str): Databases that need to be manipulated.

Timeout (int): The query timed out in a few seconds, with a default value of 0 infinite waits.

Login_timeout (int): Connection time-out and logon timeout, the default value is 60.

CharSet (str): The character set used by the connection database.

As_dict (BOOL): Returns as a dictionary, default to tuples.

2. Methods of database Connection objects

Connection.close (): Closes the database connection.

Connection.cursor (): Returns a cursor object that can be used for querying and getting results from the database.

Connection.commit (): commits the current transaction. You must call this method to ensure that your data is executed.

Connection.autocommit (): The condition There is a Boolean value. This method determines whether autocommit mode is turned on or off.

3. Cusor Object method

Cursor.close (): Closes the cursor object, which can no longer be used.

Cursor.execute (): Manipulate strings and parameters.

Cursor.fetchall (): Returns the result of the query as a tuple.

PS:Http://www.pymssql.org/en/stable/ref/pymssql.html#connection-class Official document address.

Third, the example shows

There are two tables in the SQL Server database. A saved user name and password, and a log in. If a person logged in error three times the "account has been locked, please contact my administrator to unlock", if successful login displays "Login successful",

If the account or password entered is incorrect, the user name or password error is displayed.

Member table record account password:

The list table records the logon log:

The specific code shows as follows:

Python Operations SQL Server