How to read the database in python

Use python to read data in MS-SQL. The template pymssql is used here.

Because it is not a template that comes with python, you first need to use pip to install it. The corresponding command is: pip install pymssql

Create the main.py file and enter the code as follows:

import pymssql,os

server="127.0.0.1"  #服务器IP或服务器名称
user="sa"           #登陆数据库所用账号
password="password" #该账号密码
conn=pymssql.connect(server,user,password,database='master')
cursor=conn.cursor()
cursor.execute("select name from sys.databases") #向数据库发送SQL命令
row=cursor.fetchone()
while row:
    print row[0]
    row=cursor.fetchone()
conn.close()

Through the loop command, the obtained results are displayed one by one.

Click Run to execute the program and the query results of the SQL command can be returned normally.

The following insert command in SQL language is used to change the database operated in the previous code from "master" to "test", and the query command followed by cursor.execute is changed to an insert command in insert format:

import pymssql

server="127.0.0.1"  #服务器IP或服务器名称
user="sa"           #登陆数据库所用账号
password="password" #该账号密码
conn=pymssql.connect(server,user,password,database='test')
cursor=conn.cursor()
cursor.execute("insert into dbo.test ([NO.],Name,Address) values ('003','张三','郑州') ")

After executing the command, no data was inserted into the database. This is because when executing the update, insert, and delete commands, a conn.commit() command needs to be added to allow the database to execute the statement.

import pymssql

server="127.0.0.1"  #服务器IP或服务器名称
user="sa"           #登陆数据库所用账号
password="password" #该账号密码
conn=pymssql.connect(server,user,password,database='test')
cursor=conn.cursor()
cursor.execute("insert into dbo.test ([NO.],Name,Address) values ('003','张三','郑州') ")
conn.commit()
conn.close()

It is important to note that pymssql cannot open a database named in Chinese, so when using pymsslq to interact with the database, the database accessed must be a database named in English.

Use py2exe or pyinstaller to package the written python program. You need to add import decimal at the beginning of the program and import the decimal template, otherwise an error will be reported when running.

For more Python related technical articles, please visit the Python Tutorial column to learn!

The above is the detailed content of How to read the database in python. For more information, please follow other related articles on the PHP Chinese website!