price latitude longitudeĠ 3526 HIGH ST SACRAMENTO 95838. Once the above code runs without any error, I would suggest a few basic checkups to see everything is done as intended.įirst check if the csv file is successfully and the data is stored to the variable 'df'. As you may guess here, the queried SQL result is returned as a list. SqlList = sqlEngine.execute("SELECT * FROM TransactionDB").fetchall() : this line mean 'execute the specified SQL query and save it to the variable 'sqlList'. if_exists='replace' mean 'if there is already a table named 'TransactionDB', replace the table with this new data. From follow on, you will get access to the SQL engine using this name.ĭf.to_sql('TransactionDB', con=sqlEngine, if_exists='replace') : this line mean 'convert the panda table named 'df' into an SQL table named 'TransactionDB'. SqlEngine = create_engine('sqlite://', echo=False) : this create an SQL engine and name it as 'sqlEngine'. For the simplicity, you can create your own / simple table by yourself if you like. You can get this csv file as described in this page. In this example, I am creating a panda table by reading an existing csv file by pd.read_csv('Sacramentorealestatetransactions.csv'). SqlList = sqlEngine.execute("SELECT * FROM TransactionDB").fetchall() SqlEngine = create_engine('sqlite://', echo=False)ĭf.to_sql('TransactionDB', con=sqlEngine, if_exists='replace') Installing collected packages: sqlalchemyįollowing is a short example that connect to an sql engine and convert a panda table to the sql table, retrieve data from the table.ĭf = pd.read_csv('Sacramentorealestatetransactions.csv') (At the time of writing this note (Jul 2020), I was using Python 3.7.5 and the installed sqlalchemy version is shown below).ĭownloading SQLAlchemy-1.3.18-cp37-cp37m-win_amd64.whl (1.2 MB) This page is to show you how to convert the panda table (DataFrame) to sql database and manipulate the data using SQL.įirst, you need to install a sql engine and that can be installed as followed. Panda DataFrame itself provide many powerful tools for data manipulation and simple to use, but depending on the skillset which you are more familiar with, you might have thought 'it would be good if I can use sql to the panda table'.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |