import pyodbc
# Database Server Information
driver = 'SQL Server'
server = 'SHCWSYWFZ46T'
user = 'sa'
password = 'sh.0628'
database = 'zyroi'
# Connect to the database
conn = pyodbc.connect(driver=driver, server=server, user=user, \
password=password, database=database)
'''
There are two ways to store data in excel
'''
'''Method 1:
//Use read_sql of pandas to store select query results in DataFrame and export them to excel
'''
import pandas as pd
df=pd.read_sql(sql="SELECT top 1000 * FROM\
zyroi.dbo.[roi]\
order by Month DESC",con=conn)
df.to_excel(r'd:\Users\zhanggl21\Desktop\Clean the telephone meter to get the number.xlsx')
'''Method 2:
//Using loops and excel packages
'''
cur = conn.cursor()
sql = 'select top 100 * from [201711qxcall]' # Query statement
cur.execute(sql)
rows = cur.fetchall() #Get all the data, return a list object, and each row is a tuple tuple
#Close database connection
conn.close()
import xlwt
wk=xlwt.Workbook()
sht=wk.add_sheet('database',cell_overwrite_ok=False)
#Record field name information (column names of tables)
biaoti=cur.description
# Write field information
for bt in range(0,len(biaoti)):
sht.write(0,bt,biaoti[bt][0])
help(sht.write)
# Get and write data segment information
row = 1
col = 0
for row in range(1,len(rows)+1):
for col in range(0,len(biaoti)):
sht.write(row,col,u'%s' %(rows[row-1][col]))
wk.save(r'd:\Users\zhanggl21\Desktop\sy.xls')
1,Connect to the database
1)Connect directly to the database and create a cursor( cursor)
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()
2)Use DSN Connect. usually DSN Connection does not require a password, or does it need to provide a password? PSW Keyword.
cnxn = pyodbc.connect('DSN=test;PWD=password')
cursor = cnxn.cursor()
//There are more options for connection functions, and you can see more details in the pyodbc document, connect funtion and Connection Strings
2,Data query( SQL The statement is select ...from..where)
1)Be-all SQL Statements are used cursor.execute Function runs. If a statement returns rows, such as those returned by a query statement, you can use the cursor fetch Functions to get data, these functions are( fetchone,fetchall,fetchmany).If the empty line is returned, fetchone The function returns None,and fetchall and fetchmany An empty column will be returned.
cursor.execute("select user_id, user_name from users")
row = cursor.fetchone()
if row:
print row
2)Row This class is similar to a tuple, but they can also be accessed by field names.
cursor.execute("select user_id, user_name from users")
row = cursor.fetchone()
print 'name:', row[1] # access by column index
print 'name:', row.user_name # or access by name
3)If all rows are retrieved, then fetchone Will return None.
while 1:
row = cursor.fetchone()
if not row:
break
print 'id:', row.user_id
4)Use fetchall When the function is used, all the remaining rows are returned, and if they are empty, an empty column is returned. (If there are many rows, this will take up a lot of memory. Unread rows will be compressed and stored in the database engine and sent in batches by the database server. Reading only the rows you need at a time will save a lot of memory space.
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
print row.user_id, row.user_name
5)If you plan to read all the data at once, you can use it cursor In itself.
cursor.execute("select user_id, user_name from users"):
for row in cursor:
print row.user_id, row.user_name
6)Because cursor.execute Return a cursor，So you can simplify the above statement as follows:
for row in cursor.execute("select user_id, user_name from users"):
print row.user_id, row.user_name
7)abound with SQL Statements written in a single line are not very convenient, so you can also use a three-quoted string to write:
cursor.execute("""
select user_id, user_name
from users
where last_logon < '2001-01-01'
and bill_overdue = 'y'
""")
3,parameter
1)ODBC Supported by SQL A question mark is used as a parameter in the statement. You can be at SQL The statement is followed by a value to be passed to SQL Question marks in statements.
cursor.execute("""
select user_id, user_name
from users
where last_logon < ?
and bill_overdue = ?
""", '2001-01-01', 'y')
//This is safer than writing values directly in SQL statements, because each parameter is passed to the database individually. It's also more efficient if you run the same SQL statement with different parameters.
3)python DB API When specifying multiple parameters, a sequence can be used to pass them. pyodbc Similarly, it supports:
cursor.execute("""
select user_id, user_name
from users
where last_logon < ?
and bill_overdue = ?
""", ['2001-01-01', 'y'])
cursor.execute("select count(*) as user_count from users where age > ?", 21)
row = cursor.fetchone()
print '%d users' % row.user_count
4,Data insertion
1)Data insertion SQL Insert statement passed to cursor Of execute Function, which can be accompanied by any required parameter.
cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()
cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
cnxn.commit()
//Note the call to the cnxn.commit() function: You must call the commit function, otherwise all your operations on the database will fail! When disconnected, all suspension modifications will be reset. This can easily lead to errors, so you must remember to call the commit function.
5,Data modification and deletion
1)Data modification and deletion is the same as the above operation. SQL Statement passed to execute Function. But we often wonder how many records are affected by data modification and deletion. You can use this time. cursor.rowcount The return value of the.
cursor.execute("delete from products where id <> ?", 'pyodbc')
print cursor.rowcount, 'products deleted'
cnxn.commit()
2)Because execute Functions always return cursor，So sometimes you can see something like this: (Note) rowcount Put it at the end.
deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
cnxn.commit()
//Also note the call to the cnxn.commit() function
6,Tips
1)Because of the use of single quotation marks SQL Statements are valid, so are double quotation marks:
deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
2)If you use three quotation marks, you can use them as well.
deleted = cursor.execute("""
delete
from products
where id <> 'pyodbc'
""").rowcount
3)Some databases (for example SQL Server)Column names are not generated when counting. In this case, subscripts must be used if you want to access data. Of course, you can also use it.“ as"Keyword to get a column name (below) SQL Statement“ as name-count")
row = cursor.execute("select count(*) as user_count from users").fetchone()
print '%s users' % row.user_count
4)If you just need a value, you can use it in the same board. fetch Function to get all the data for the row and the first column.
count = cursor.execute("select count(*) from users").fetchone()[0]
print '%s users' % count
//If null, this statement will not run. The fetchone() function returns None, and you will get an error: NoneType does not support subscripts. If you have a default value, you can often use ISNULL or merge NULLs directly into the SQL database to override the default value.
maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
//In this example, if max(id) returns NULL, coalesce(max(id),0) will result in a query value of 0.
'''
What is the cursor in Sql?
In databases, cursors provide a flexible means of manipulating data retrieved from tables.
Essentially, a cursor is actually a result set that can be extracted from multiple data records at a time.
A recording mechanism.
A cursor is always associated with an SQL selection statement because the cursor is a result set (which can be zero, one or more).
It consists of multiple records retrieved by relevant selection statements) and cursor positions in the result set pointing to specific records.
The operation of the cursor on the database will affect the whole row set. Row set returned by SELECT statement
Includes all rows that satisfy the conditions in the WHERE clause of the statement. The complete set of rows returned by the statement is called the result set.
Applications, especially interactive online applications, do not always treat the entire result set as a unit
To deal with effectively. These applications need a mechanism to process one or part of a row at a time.
A cursor is a result set extension that provides this mechanism.
The cursor extends the result processing in the following ways:
1. Allow locating specific rows in the result set.
2. Retrieve one or more rows from the current location of the result set.
3. Support data modification of rows in the current location of the result set.
4. Provide different levels of visibility support for changes made by other users to database data displayed in the result set.
5. Provide Transact-SQL statements that access data in the result set used in scripts, stored procedures, and triggers.
'''