Learning Pandas (ix)

Original English: 09-lesson

Export data from Microsoft's SQL database to CSV, Excel, or text files.

# import library Import
pandas as PD
import sys from
sqlalchemy import create_engine, MetaData, Table, select

Print (' Python version ' + sys.version)
print (' Pandas version ' + pd.__version__)

Python version 3.6.1 | Packaged by Conda-forge | (Default, Mar 2017, 21:57:00) 
[GCC 4.2.1 compatible Apple LLVM 6.1.0 (clang-602.0.53)]
Pandas version 0.19.2

fetching data from a SQL database

In this section we use SQLAlchemy to crawl data from the SQL database. Please note that database parameters you need to modify yourself.

# parameters, modified into your own database, server and table TableName = "Data" DB = {' drivername ': ' Mssql+pyodbc ', ' Servernam E ': ' David-think ', # ' port ': ' 5432 ', # ' username ': ' Lynn ', # ' password ': ', ' database ': ' Bizintel ', ' dri 
Ver ': ' SQL Server Native Client 11.0 ', ' trusted_connection ': ' Yes ', ' legacy_schema_aliasing ': False} # Create a database connection Engine = Create_engine (db[' drivername '] + '://' + db[' servername '] + '/' + db[' database ' + ' + ' + ' driver= ' + db[' driver ' ] + '; ' + ' trusted_connection= ' + db[' trusted_connection '], legacy_schema_aliasing=db[' legacy_schema_aliasing '] conn = Engine.connect () # The configuration required for the query table metadata = METADATA (conn) # tables to query TBL = table (tablename, metadata, Autoload=true dbo ") #tbl. Create (checkfirst=true) # Select All sql = Tbl.select () # Execute SQL code result = conn.execute (SQL) # Data in a DF = PD in frame. Dataframe (Data=list (Result), Columns=result.keys ()) # Close connection conn.close () print (' Done ') 

    Done

All exported files will be saved to the same directory as notebook. Export to CSV file

Df.to_csv (' Dimdate.csv ', index=false)
print (' Done ')

Done

exporting to Excel files

Df.to_excel (' Dimdate.xls ', index=false)
print (' Done ')

Done

Export to TXT text file

Df.to_csv (' DimDate.txt ', index=false)
print (' Done ')

Done