Codebase list pyexcel / 77f53c9b-c4a9-43cd-977c-38378a9b0eb0/upstream docs / source / database.rst
77f53c9b-c4a9-43cd-977c-38378a9b0eb0/upstream

Tree @77f53c9b-c4a9-43cd-977c-38378a9b0eb0/upstream (Download .tar.gz)

database.rst @77f53c9b-c4a9-43cd-977c-38378a9b0eb0/upstreamview markup · raw · history · blame

Working with databases

How to import an excel sheet to a database using SQLAlchemy

Note

You can find the complete code of this example in examples folder on github

Before going ahead, let's import the needed components and initialize sql engine and table base:

>>> import os
>>> import pyexcel as p
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy import Column , Integer, String, Float, Date
>>> from sqlalchemy.orm import sessionmaker
>>> engine = create_engine("sqlite:///birth.db")
>>> Base = declarative_base()
>>> Session = sessionmaker(bind=engine)

Let's suppose we have the following database model:

>>> class BirthRegister(Base):
...     __tablename__='birth'
...     id=Column(Integer, primary_key=True)
...     name=Column(String)
...     weight=Column(Float)
...     birth=Column(Date)

Let's create the table:

>>> Base.metadata.create_all(engine)

Now here is a sample excel file to be saved to the table:

System Message: INFO/1 (<string>, line 40)

No directive entry for "pyexcel-table" in module "docutils.parsers.rst.languages.en". Trying "pyexcel-table" as canonical directive name.

System Message: ERROR/3 (<string>, line 40)

Unknown directive type "pyexcel-table".

.. pyexcel-table::

   ---pyexcel:data table---
   name,weight,birth
   Adam,3.4,2015-02-03
   Smith,4.2,2014-11-12

System Message: INFO/1 (<string>, line 47)

No directive entry for "testcode" in module "docutils.parsers.rst.languages.en". Trying "testcode" as canonical directive name.

System Message: ERROR/3 (<string>, line 47)

Unknown directive type "testcode".

.. testcode::
   :hide:

   >>> import datetime
   >>> data = [
   ...    ["name", "weight", "birth"],
   ...    ["Adam", 3.4, datetime.date(2015, 2, 3)],
   ...    ["Smith", 4.2, datetime.date(2014, 11, 12)]
   ... ]
   >>> p.save_as(array=data, dest_file_name="birth.xls")

Here is the code to import it:

>>> session = Session() # obtain a sql session
>>> p.save_as(file_name="birth.xls", name_columns_by_row=0, dest_session=session, dest_table=BirthRegister)

Done it. It is that simple. Let's verify what has been imported to make sure.

>>> sheet = p.get_sheet(session=session, table=BirthRegister)
>>> sheet
birth:
+------------+----+-------+--------+
| birth      | id | name  | weight |
+------------+----+-------+--------+
| 2015-02-03 | 1  | Adam  | 3.4    |
+------------+----+-------+--------+
| 2014-11-12 | 2  | Smith | 4.2    |
+------------+----+-------+--------+

System Message: INFO/1 (<string>, line 76)

No directive entry for "testcode" in module "docutils.parsers.rst.languages.en". Trying "testcode" as canonical directive name.

System Message: ERROR/3 (<string>, line 76)

Unknown directive type "testcode".

.. testcode::
   :hide:

   >>> session.close()
   >>> os.unlink('birth.xls')
   >>> os.unlink('birth.db')