3
$\begingroup$

What is the best way to read SQL database in to Tensorflow?

Currently, I am using Postgres on server and developed DL algorithm on Tensorflow using Jupyter Lab. How can I import data into Jupyter Lab using tf.data API? I do not want to store the data in the disk and keep running the algorithm when the new data arrives.

It seems like tf.data.experimental.SqlDataset only support for sqlite.

(NOTE: I did not upgrade my Tensorflow, so, I am using tf.contrib.data.SqlDataset() for the minimal working example.)

I migrated the data from PostgreSQL to SQLite3 and using

#Ignore the warnings
import warnings
warnings.filterwarnings("ignore")

import tensorflow as tf
#To start an input pipeline, you must define a source
dataset = tf.contrib.data.SqlDataset("sqlite", "/home/musara1/musara_dev.sqlite3",
                                 "SELECT * FROM basetable LIMIT 10",
                                 (tf.string, tf.int32, tf.int32, tf.int32, tf.int32, tf.int32, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.int32, tf.int32, tf.int32))

iterator = dataset.make_one_shot_iterator()
next_element = iterator.get_next()
# Prints the rows of the result set of the above query.
sess=tf.InteractiveSession()

print(sess.run(next_element))

I can print the next element. However, there are other transformations I need to do on the dataset. such as splitting into training/validation/testing and getting rid of some columns et cetera. However, the output of tf.contrib.data.SqlDataset() is for me <SqlDataset shapes: ((), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), ()), types: (tf.string, tf.int32, tf.int32, tf.int32, tf.int32, tf.int32, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.int32, tf.int32, tf.int32)>

I have 25 columns and tf.contrib.data.SqlDataset() creates 25 different tensorflow.python.framework.ops.Tensor. How can I bring them together? Therefore, I can use tf.data.Dataset.from_tensor_slices()?

$\endgroup$
2
  • $\begingroup$ So just to confirm, you've already migrated entire dataset from PostgreSQL to SQLite3, right? If not, you may like to go through this, as you've already noticed SQLite is a better companion with TF. $\endgroup$ Nov 24, 2018 at 6:22
  • $\begingroup$ Yes I did. My problem is how to manipule SqlDataset on Tensorflow using tf.data API $\endgroup$
    – ARAT
    Nov 24, 2018 at 14:35

4 Answers 4

1
$\begingroup$

You use the methods on SqlDataset to manipulate the data. For example, create a train/test split with:

test_dataset = dataset.take(1000) 
train_dataset = dataset.skip(1000)

I would get rid of unneeded columns in the SELECT statement to reduce the size of the data as early as possible.

$\endgroup$
0
$\begingroup$

That import step doesn't look right. Can you try it like this?

import pypyodbc 
cnxn = pypyodbc.connect("Driver={SQL Server Native Client 11.0};"
                        "Server=ServerName;"
                        "Database=TestDB;"
                        "Trusted_Connection=yes;")

cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Actions')

for row in cursor:
    print('row = %r' % (row,))
$\endgroup$
1
  • $\begingroup$ yes I could use external library which I did in the end using psycopg2 but I wanted to use Tensorflow's its own function and manipulate there using tf.data API. $\endgroup$
    – ARAT
    Feb 22, 2020 at 14:18
0
$\begingroup$

I'm late to the party but for anyone reading this in the future: SQLAlchemy is a great way to handle any SQL interfaces. Just create a model class that matches your DB schema, then use the query API to get your data.

If you have very large datasets, you can use use yield_per or slice to easily retrieve it in chunks, as de

$\endgroup$
0
$\begingroup$

here is a link for the driver-installation on possible linux distros. and another link with installation und connection examples. my code looks like as following,

import pandas as pd # data processing
import pyodbc
pyodbc.drivers()

server = 'MY_SERVER' 
database = 'MY_DB' 
username = 'DB_USER' 
password = 'DB_PASS'  
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                  'SERVER='+server+
                  ';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

query = 'SELECT * FROM XXX'
sales = pd.read_sql(query, cnxn)
print(sales.head(26))
print(sales.info())
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.