Create a table with Primary Key

[xuanblo]

I want to add a primary key when creating a table use SimpleSQlite. Could you tell me how to do this？

[thombashi]

Hi @xuanblo,

If you use ORM feature, you can create a table with a primary key as follows:

from simplesqlite import connect_memdb
from simplesqlite.model import Integer, Model, Real, Text


class Sample(Model):
    foo_id = Integer(primary_key=True)  # set as the primary key
    name = Text(not_null=True, unique=True)
    value = Real()


def main():
    con = connect_memdb()

    Sample.attach(con)
    Sample.create()
    Sample.insert(Sample(name="abc", value=0.1))
    Sample.insert(Sample(name="xyz", value=1.11))
    Sample.insert(Sample(name="bar", value=2.22))

    print(Sample.fetch_schema().dumps())
    print("records:")
    for record in Sample.select():
        print("    {}".format(record))

.. table:: sample

    +---------+-------+-----------+--------+------+-----+
    |Attribute| Type  |PRIMARY KEY|NOT NULL|UNIQUE|Index|
    +=========+=======+===========+========+======+=====+
    |foo_id   |INTEGER|X          |        |      |     |
    +---------+-------+-----------+--------+------+-----+
    |name     |TEXT   |           |X       |X     |     |
    +---------+-------+-----------+--------+------+-----+
    |value    |REAL   |           |        |      |     |
    +---------+-------+-----------+--------+------+-----+


records:
    Sample: foo_id=1, name=abc, value=0.1
    Sample: foo_id=2, name=xyz, value=1.11
    Sample: foo_id=3, name=bar, value=2.22

Please provide me some examples if you creating a table with other methods of the SimpleSQLite class.

[xuanblo]

Hi @thombashi,
Thank you for your help. I have a gene annotation file in table format. I want to write the data into Sqlite3 and generate django models use inspectdb. But django model class need a primary key. I think SimpleSQLite help a lot. Here is my code.

from simplesqlite import connect_memdb
from simplesqlite.model import Integer, Model, Real, Text

from simplesqlite import SimpleSQLite
import pandas as pd
import os

anno_file_path = '../Rawdata/Integrated_Function.annotation.xls'

db_name = 'integratedFunction'
db_file_path = os.path.join('../Results', db_name + '.sqlite3')

# if database exists than delete
if os.path.exists(db_file_path):
    os.remove(db_file_path)

# read annotation file
anno_df = pd.read_csv(anno_file_path, sep='\t', index_col = None, header = 0)
# remove '#'
anno_df.rename(columns={anno_df.columns[0]:anno_df.columns[0].strip('#')}, inplace = True)


class Sample(Model):
    GeneID = Text(primary_key=True, not_null=True)  # set as the primary key
    GO_annotation = Text()
    KEGG_annotation = Text()
    KOG_class = Text()
    KOG_class_annotation = Text()
    TrEMBL_annotation = Text()
    nr_annotation = Text()


def main():
    # create object
    con = SimpleSQLite(db_file_path, 'w')

    Sample.attach(con)
    Sample.create()
    
    rod = anno_df.T.to_dict().values()
    con.insert_many(table_name='sample', record_list=rod)
    
    print(Sample.fetch_schema().dumps())
    print("records:")
    for record in Sample.select():
        print("    {}".format(record))
    con.close()

But i really hope it can be much easier like this:

# ...
con.create_table_from_dataframe(anno_df, table_name = db_name, index_attr_list = anno_df.columns, primary_key=anno_df.columns[0])

[thombashi]

Hi @xuanblo,

Thank you for your additional information.
I could understand what you are trying to do, and I had implemented the feature.

con.create_table_from_dataframe(anno_df, table_name = db_name, index_attr_list = anno_df.columns, primary_key=anno_df.columns[0])

You can use the above syntax (primary_key argument for create_table_from_xxx methods) since SimpleSQLite 0.32.0 Please try this version.

[xuanblo]

Many thanks!

[thombashi]

I'll close the issue.
Feel free to reopen if you still have any problems about the issue.